I’m trying to parse data that I’ve received from an excel file in Python. For this I am using the xlrd library. I have a cell in Excel whose value is 5/16/2016 12:15 and I receive it in Python as 42506.6493. I understand that Excel saves the date as the number of days since 1/1/1900. So in Python I’m trying to add this number of days (just days for now without the fraction representing the time) to get the same date, using the code below:
orgDate = datetime.datetime(1900,1,1,0,0,0,0)
xlVal = 42506.6493
newDate = orgDate + datetime.timedelta(days=int(xlVal))
However when I read the value of newDate I find it to be datetime.datetime(2016, 5, 18, 0, 0) whereas it should be May 16 not 18. Anybody knows how to handle this?
Please consult the xlrd documentation, particularly the section on dates in Excel.
Dates don't really start at 1900-01-01. You have a two-day difference because (1) Excel preserves the Lotus 1-2-3 bug which considers 1900 a leap year and (2) even if dates did start at 1900-01-01, then that would make 1900-01-01 day 1, not day 0, so you would need to adjust your timedelta accordingly.
But really, just save yourself the trouble and use xlrd's built-in date facilities, xldate_as_tuple or xldate_as_datetime.