Python datetime to Excel serial date conversion-CodePudding

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

CodePudding user response：

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