How to convert MS Excel numbers in general format (e.g 43390) to date in python

Question

please I need solution to this problem I have a field that is formatted in e.g 43390 which is general date format in excel. I need to format it to a date like "d/m/yyy"

here is the code I wrote :

trans_ data['DATE'] = pd.to_ datetime(trans_ data['DATE'], format='%d-%m-%Y')

but I have this error:

ValueError: time data '43390' does not match format '%d-%m-%Y' (match)

What is the date corresponding to 43390? 17/10/2018? – Corralien Sep 06 '21 at 20:03 — Corralien, Sep 06 '21 at 20:03

score 0 · Answer 1 · answered Sep 06 '21 at 20:13

0

I tried to convert the "43390" in LibreOffice and it converted it to 2018-10-17 (the origin "0" is "30/12/1899"):

origin = pd.Timestamp("30/12/1899")
df["col"] = df["col"].apply(lambda x: origin + pd.Timedelta(days=x))
print(df)

Prints:

         col
0 2018-10-17
1 2019-11-02

df used:

     col
0  43390
1  43771

Screenshot:

answered Sep 06 '21 at 20:13

Andrej Kesely

168,389
15
48
91

1

You can do without the apply, pd.to_datetime has all you need - see the dupe I linked. – FObersteiner Sep 07 '21 at 07:22
@MrFuppes Yep, that answer is more to the depth :) Thanks! – Andrej Kesely Sep 07 '21 at 08:12

score 0 · Answer 2 · answered Sep 06 '21 at 20:13

0

The following code might help you.

from datetime import timedelta
import pandas as pd

excel_date = '43390'
excel_date = int(excel_date)
python_date = pd.to_datetime('1900-01-01') + timedelta(excel_date-2)

print(python_date)

The python_date object stores the date. Then you can change the format to the format you need.

answered Sep 06 '21 at 20:13

GabrielP

777
4
8

pandas also has a timedelta type. Other than that, you actually don't need it, see the dupe I linked. – FObersteiner Sep 07 '21 at 07:21

How to convert MS Excel numbers in general format (e.g 43390) to date in python

2 Answers2