CODEWITHSUNDEEP
pythonexceldateformat
Alex fulleda
Alex fulleda

Reputation: 3

Strange date format conversion

I have a db with dates in this format: 44378.097351.

If I put that in excel and choose the date format is says it is 01/07/2021 2:20:11, which is correct. The problem is that now I need to do a plot with python with these dates and I extract the data directly from the db but I don't know how to convert it to an understandable date format.

Any suggestions?

Thank you

Upvotes: 0

Views: 495

Answers (2)

Jason Pan
Jason Pan

Reputation: 792

From Date systems in Excel, we can find how such a number was converted to a datetime.

The 1900 date system In the 1900 date system, dates are calculated by using January 1, 1900, as a starting point. When you enter a date, it is converted into a serial number that represents the number of days elapsed since January 1, 1900. For example, if you enter July 5, 2011, Excel converts the date to the serial number 40729. This is the default date system in Excel for Windows, Excel 2016 for Mac, and Excel for Mac 2011. If you choose to convert the pasted data, Excel adjusts the underlying values, and the pasted dates match the dates that you copied.

from datetime import timedelta, datetime
dt = datetime(1900, 1, 1) + timedelta(44378.097351 - 2)

Upvotes: 1

Avandale
Avandale

Reputation: 262

This format corresponds to the number of days since 01/01/1900.

you should check out this or this post to find an adequate solution ;)

Upvotes: 0

Related Questions