Pandas converts the date column to an integer and displaying the whole column as integer values-CodePudding

I tried to pull an excel where I have a column as "ORDER LINE CREATE DATE" which consists of date values in MM/DD/YYYY format.

Now when I use the pd.read_excel('File path') it treats the date column as an integer value and displays an integer instead of date time for ex. the date 7/15/2022 when imported is automatically converted to the value 44757 and similarly the date 7/14/2022 when imported gets automatically converted to the value 44756

What is causing this problem and how can I import the date in the same format as that in the excel, I tried using dtype={'ORDER LINE CREATE DATE':"string"} and even dtype={'ORDER LINE CREATE DATE':"datetime64"} during the excel import but it still fails. Thanks!

CodePudding user response：

Can you try the following:

from datetime import datetime

df = pd.read_excel('File path')
df['ORDER LINE CREATE DATE'] = df['ORDER LINE CREATE DATE'].apply(
   lambda x: datetime.fromordinal(datetime(1900, 1, 1).toordinal()   int(x) - 2))