Python datetime to Excel serial date conversion

Question

The following code converts a string into a timestamp. The timestamp comes out to: 1646810127.

However, if I use Excel to convert this date and time into a float I get: 44629,34. I need the Excel's output from the Python script.

I have tried with a few different datetime strings to see if there is any pattern in between the two numbers, but cannot seem to find any.

Any thoughts on how I get the code to output 44629,34?

Much appreciated

import datetime

date_time_str = '2022-03-09 08:15:27'
date_time_obj = datetime.datetime.strptime(date_time_str, '%Y-%m-%d %H:%M:%S')

print('Date:', date_time_obj.date())
print('Time:', date_time_obj.time())
print('Date-time:', date_time_obj)

print(date_time_obj.timestamp())

>>output:
Date: 2022-03-09
Time: 08:15:27
Date-time: 2022-03-09 08:15:27
1646810127.0

btw. I'd suggest to change the title of the question to "Python datetime to Excel serial date conversion" (or similar) for findability of the question. It's not actually about formatting in the sense of producing a certain string representation. — FObersteiner
– FObersteiner, Commented Mar 9, 2022 at 10:30
I have edited the title according to your suggestion. Thank you once again for you assistance :) — AlbaTroels
– AlbaTroels, Commented Mar 9, 2022 at 10:58

FObersteiner · Accepted Answer · 2022-03-09 09:59:15Z

1

calculate the timedelta of your datetime object versus Excel's "day zero", then divide the total_seconds of the timedelta by the seconds in a day to get Excel serial date:

import datetime

date_time_str = '2022-03-09 08:15:27'
UTC = datetime.timezone.utc

dt_obj = datetime.datetime.fromisoformat(date_time_str).replace(tzinfo=UTC)
day_zero = datetime.datetime(1899,12,30, tzinfo=UTC)

excel_serial_date = (dt_obj-day_zero).total_seconds()/86400

print(excel_serial_date)
# 44629.3440625

Note: I'm setting time zone to UTC here to avoid any ambiguities - adjust as needed.

Since the question is tagged pandas, you'd do the same thing here, only that you don't need to set UTC as pandas assumes UTC by default for naive datetime:

import pandas as pd

ts = pd.Timestamp('2022-03-09 08:15:27')

excel_serial_date = (ts-pd.Timestamp('1899-12-30')).total_seconds()/86400

print(excel_serial_date)
# 44629.3440625

2 Comments

AlbaTroels Over a year ago

Aha. Yes of course. Makes sense that they may not use the same day zero. Thank you!

FObersteiner Over a year ago

@AlbaTroes what I named "day zero" is called "epoch" most of the time I think ;-) So the "Unix epoch" would be 1970-01-01 midnight UTC.

Collectives™ on Stack Overflow

Python datetime to Excel serial date conversion

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related