2

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!

pranshu dhyani
  • 23
  • 2

1 Answers1

2

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))
Jeril
  • 7,858
  • 3
  • 52
  • 69
  • updated the solution – Jeril Aug 22 '22 at 09:23
  • 1
    This works great in cases where the excel file wasn't formatting as date properly and shows as an int (even in excel). I have files that sometimes are and sometimes aren't formatted date so I use, if df['ORDER LINE CREATE DATE'].dtype==int64: df['ORDER LINE CREATE DATE'] = df['ORDER LINE CREATE DATE'].apply( lambda x: datetime.fromordinal(datetime(1900, 1, 1).toordinal() + int(x) - 2)) – Josh Knechtel Aug 02 '23 at 14:56