[FIXED] Excel binary Date field converted to numpy int64

Issue

I have an binary excel file with DATE column with value '7/31/2020'.

Upon reading the file the DATE value is getting converted to numpy.int64 with value 44043.

Can you tell me how to stop this conversion or getting the date as is given in excel.

This is my code to read the excel file

>>df = pd.read_excel('hello.xlsb', engine='pyxlsb')
>>df[DATE][0]
>>44043

Solution

Apparently the integer value is the number of days since the 0th of January 1900. But the 0th of January doesn't exist: there seems to be a fudge factor of 2 involved here.

>>> import datetime
>>> d = datetime.date(1900, 1, 1) + datetime.timedelta(days=44043 - 2)
>>> d
datetime.date(2020, 7, 31)
>>> d.isoformat()
'2020-07-31'
>>> d.strftime("%m/%d/%Y")
'07/31/2020'

See the strftime docs for other formatting options.

Answered By - Graham501617