Converting Excel-Style Dates with Pandas

When parsing dates from Excel files, you may encounter dates represented as floating-point numbers, such as 42580.3333333333. Pandas provides a convenient way to convert these Excel dates into regular datetime objects.

To do this, you can create a TimedeltaIndex from the Excel date numbers, adding the scalar datetime for 1900,1,1 to the index. This will convert the Excel dates to the corresponding datetime objects:

import pandas as pd
df = pd.DataFrame({'date': [42580.3333333333, 10023]})
df['real_date'] = pd.TimedeltaIndex(df['date'], unit='d')   pd.datetime(1900, 1, 1)

However, it's important to note that Excel uses a different date system than Pandas, with the epoch being December 30, 1899, instead of January 1, 1900. To account for this, you may need to adjust the starting date:

df['real_date'] = pd.TimedeltaIndex(df['date'], unit='d')   pd.datetime(1899, 12, 30)