How to read dates using xlrd?

Question

This is the code where "rec" variable is used to read the dates in excel sheet but its printing float value how to print that in date format for example '2015:09:02'

for rec in sorted(out.keys()):
print rec #printing float values
print str(out[rec])

I got output:

42240.0
24

Answer 1

Excel internally stored date values as floats. So in xlrd if you want to read Excel date values as Python date values, you have to use the xldate_as_tuple method to get the date.

Documentation: http://www.lexicon.net/sjmachin/xlrd.html#xlrd.xldate_as_tuple-function

Here's a generic Example:

import datetime, xlrd
book = xlrd.open_workbook("myexcelfile.xls")
sh = book.sheet_by_index(0)
a1 = sh.cell_value(rowx=0, colx=0)
a1_as_datetime = datetime.datetime(*xlrd.xldate_as_tuple(a1, book.datemode))
print 'datetime: %s' % a1_as_datetime

If you create the file myexcelfile.xls and enter a date in cell A1 and run the above code, you should be able to see the correct datetime value in the a1_as_datetime variable.