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

Question

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 1

The offset in Excel is the number of days since 1900/01/01, with 1 being the first of January 1900, so add the number of days as a timedelta to 1899/12/31:

from datetime import datetime, timedelta  def from_excel_ordinal(ordinal, _epoch0=datetime(1899, 12, 31)):     if ordinal >= 60:         ordinal -= 1  # Excel leap year bug, 1900 is not a leap year!     return (_epoch0 + timedelta(days=ordinal)).replace(microsecond=0) 

You have to adjust the ordinal by one day 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 datetime(1900, 2, 28, 0, 0) for both 59 and 60 to correct for this, with fractional values in the range [59.0 - 61.0) all being a time between 00:00:00.0 and 23:59:59.999999 on that day.

The above also supports serials with a fraction to represent time, but since Excel doesn't support microseconds those are dropped.

Answer 2

from datetime import datetime, timedelta  def from_excel_ordinal(ordinal, epoch=datetime(1900, 1, 1)):     # Adapted from above, thanks to @Martijn Pieters       if ordinal > 59:         ordinal -= 1  # Excel leap year bug, 1900 is not a leap year!     inDays = int(ordinal)     frac = ordinal - inDays     inSecs = int(round(frac * 86400.0))      return epoch + timedelta(days=inDays - 1, seconds=inSecs) # epoch is day 1  excelDT = 42548.75001           # Float representation of 27/06/2016  6:00:01 PM in Excel format   pyDT = from_excel_ordinal(excelDT) 

The above answer is fine for just a date value, but here I extend the above solution to include time and return a datetime values as well.