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, Mar 09 '22 at 10:30
I have edited the title according to your suggestion. Thank you once again for you assistance :) — AlbaTroels, Mar 09 '22 at 10:58

score 1 · Accepted Answer · answered Mar 09 '22 at 09:59

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

Python datetime to Excel serial date conversion

1 Answers1