Apply pandas to_datetime function to excel date format

Question

i decided to practice with to_datetime function , for this purpose i have changed source format of date as an integers , here is after reading excel file, how each column looks like

import numpy as np
import pandas as pd
data =pd.read_excel("pivot.xlsx")
#data["Date"] =pd.to_datetime(data["Date"],format='%d%m%y',infer_datetime_format=True)
print(data.head())

result is :

   Order ID   Product    Category  Amount   Date         Country
0         1   Carrots  Vegetables    4270  42375   United States
1         2  Broccoli  Vegetables    8239  42376  United Kingdom
2         3    Banana       Fruit     617  42377   United States
3         4    Banana       Fruit    8384  42379          Canada
4         5     Beans  Vegetables    2626  42379         Germany

in source, dateformat of date column is following 1/6/2016 so when i have apply to_datetime function, i have got following result :

import numpy as np
import pandas as pd
data =pd.read_excel("pivot.xlsx")
data["Date"] =pd.to_datetime(data["Date"],format='%d%m%y',infer_datetime_format=True)
print(data.head())

result :
   Order ID   Product  ...                          Date         Country
0         1   Carrots  ... 1970-01-01 00:00:00.000042375   United States
1         2  Broccoli  ... 1970-01-01 00:00:00.000042376  United Kingdom
2         3    Banana  ... 1970-01-01 00:00:00.000042377   United States
3         4    Banana  ... 1970-01-01 00:00:00.000042379          Canada
4         5     Beans  ... 1970-01-01 00:00:00.000042379         Germany

how can i fix it? in excel starting date is 1900 right? how can i apply it? is it possible to return real dates from those integers?

I believe you can use [this](https://stackoverflow.com/a/65460255/8998310) — josemz, May 05 '21 at 15:45
thanks for help, but it gives me date with two days in advance — , May 05 '21 at 15:55

score 1 · Accepted Answer · answered May 05 '21 at 15:58

1

you can use the openpyxl.utils.datetime.from_excel() method.

from openpyxl.utils.datetime import from_excel


df['Date'] = df['Date'].apply(from_excel)

print(df)


   Order ID   Product    Category  Amount       Date         Country
0         1   Carrots  Vegetables    4270 2016-01-06   United States
1         2  Broccoli  Vegetables    8239 2016-01-07  United Kingdom
2         3    Banana       Fruit     617 2016-01-08   United States
3         4    Banana       Fruit    8384 2016-01-10          Canada
4         5     Beans  Vegetables    2626 2016-01-10         Germany

answered May 05 '21 at 15:58

Umar.H

22,559
7
39
74

inside bracket, what does from excel denotes? – May 05 '21 at 16:00
@datodatuashvili it denotes the improted function from `openpyxl` see [the source code](https://openpyxl.readthedocs.io/en/stable/_modules/openpyxl/utils/datetime.html#from_excel) here `.apply` is just applying the funcion on the entire row iteratively. – Umar.H May 05 '21 at 16:06

Apply pandas to_datetime function to excel date format

1 Answers1