Excel Datetime SN Conversion in Python

Question

My csv input file sometimes has excel serial numbers in the date field. I am using the following code as my input file should never contain dates prior to 01/2000. However, this solution is quite time consuming and I am hoping to find a better solution. Thank you.

def DateCorrection(x):
    if pd.to_datetime(x) < pd.to_datetime('2000-01-01'):
        return pd.to_datetime(datetime.fromordinal(datetime(1900, 1, 1).toordinal() + int(x) - 2))
    else:
        return pd.to_datetime(x)

Answer 1

Assuming your input looks like

import pandas as pd
df = pd.DataFrame({'date': ["2020-01-01", 43862, "2020-03-01"]})

you can process it as follows:

# convert everything first, ignore invalid results for now:
df['datetime'] = pd.to_datetime(df['date'])

# where you have numeric values, i.e. "excel datetime format":
nums = pd.to_numeric(df['date'], errors='coerce') # timestamp strings will give NaN here

# now replace the invalid dates:
df.loc[nums.notna(), 'datetime'] = pd.to_datetime(nums[nums.notna()], unit='d', origin='1899-12-30')

...giving you

df
          date   datetime
0  2020-01-01 2020-01-01
1       43862 2020-02-01
2  2020-03-01 2020-03-01

related:

• Python pandas: how to obtain the datatypes of objects in a mixed-datatype column?
• Convert Excel style date with pandas.