Vladislav Ladenkov Vladislav Ladenkov - 1 month ago 20
Python Question

Extracting date from excel numbers

I have numbers

array([40472, 40652, 40472, ..., 40282, 40412, 40044])
,
which, at first, were stored in Excel(Libre Office).Trying to transform these number directly in Libre Office ends with very stange results.
So I want to have an easy transform in python from this type to python date and vice versa. How can i do it?

Answer

Excel converts dates to numbers by using serial numbers. See, for example, comments under the DATEVALUE function:

Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900.

You can thus find the proper date by adding that number of days. I am subtracting because it is not zero-indexed:

>>> from datetime import datetime, timedelta
>>> start = datetime(1899, 12, 30)
>>> start + timedelta(42677)
datetime.datetime(2016, 11, 3, 0, 0)

You could make a function as follows:

def excel_serial_to_datetime(n):
    start = datetime(1899, 12, 30)
    return start + timedelta(n)

NOTE: this will not work for decimal numbers, which will occur if you are more specific than the day.