How do I read a date in Excel format in Python?

Question

How can I convert an Excel date (in a number format) to a proper date in Python?

Answer 1

You can use xlrd.

From its documentation, you can read that dates are always stored as numbers; however, you can use xldate_as_tuple to convert it to a python date.

Note: the version on the PyPI seems more up-to-date than the one available on xlrd's website.

Answer 2

After testing and a few days wait for feedback, I'll svn-commit the following whole new function in xlrd's xldate module ... note that it won't be available to the diehards still running Python 2.1 or 2.2.

## # Convert an Excel number (presumed to represent a date, a datetime or a time) into # a Python datetime.datetime # @param xldate The Excel number # @param datemode 0: 1900-based, 1: 1904-based. # <br>WARNING: when using this function to # interpret the contents of a workbook, you should pass in the Book.datemode # attribute of that workbook. Whether # the workbook has ever been anywhere near a Macintosh is irrelevant. # @return a datetime.datetime object, to the nearest_second. # <br>Special case: if 0.0 <= xldate < 1.0, it is assumed to represent a time; # a datetime.time object will be returned. # <br>Note: 1904-01-01 is not regarded as a valid date in the datemode 1 system; its "serial number" # is zero. # @throws XLDateNegative xldate < 0.00 # @throws XLDateAmbiguous The 1900 leap-year problem (datemode == 0 and 1.0 <= xldate < 61.0) # @throws XLDateTooLarge Gregorian year 10000 or later # @throws XLDateBadDatemode datemode arg is neither 0 nor 1 # @throws XLDateError Covers the 4 specific errors  def xldate_as_datetime(xldate, datemode):     if datemode not in (0, 1):         raise XLDateBadDatemode(datemode)     if xldate == 0.00:         return datetime.time(0, 0, 0)     if xldate < 0.00:         raise XLDateNegative(xldate)     xldays = int(xldate)     frac = xldate - xldays     seconds = int(round(frac * 86400.0))     assert 0 <= seconds <= 86400     if seconds == 86400:         seconds = 0         xldays += 1     if xldays >= _XLDAYS_TOO_LARGE[datemode]:         raise XLDateTooLarge(xldate)      if xldays == 0:         # second = seconds % 60; minutes = seconds // 60         minutes, second = divmod(seconds, 60)         # minute = minutes % 60; hour    = minutes // 60         hour, minute = divmod(minutes, 60)         return datetime.time(hour, minute, second)      if xldays < 61 and datemode == 0:         raise XLDateAmbiguous(xldate)      return (         datetime.datetime.fromordinal(xldays + 693594 + 1462 * datemode)         + datetime.timedelta(seconds=seconds)         )