Krish Krish - 11 months ago 71
Python Question

How to convert a given ordinal number (from Excel) to a date

I have a Value 38142 I need to convert it into date format using python.
if use this number in excel and do a right click and format cell at that time the value will be converted to 04/06/2004 and I need the same result using python. How can I achieve this

Answer Source

The offset in Excel in from 1900/01/01; add the number of days as a timedelta:

from datetime import date, timedelta

def from_excel_ordinal(ordinal, _epoch=date(1900, 1, 1)):
    if ordinal > 59:
        ordinal -= 1  # Excel leap year bug, 1900 is not a leap year!
    return _epoch + timedelta(days=ordinal - 1)  # epoch is day 1

I had to adjust the ordinal by 2 for any date after 1900/02/28; Excel has inherited a leap year bug from Lotus 1-2-3 and treats 1900 as a leap year. The code above returns date(1900, 2, 28) for both 59 and 60 to correct for this.