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

Just for reference, you have two other options beside xlrd.

First is using another module called pyExcelerator. xlrd is direct offspring of this module, which is no longer in active development.

The second is communicating with Excel via COM (find more about it here). A word of warning: COM is VERY slow and not really usable for large number of operations. I've quickly switched to pyExcelerator after trying COM.

Answer 3

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)
        )

Answer 4

Here's the bare-knuckle no-seat-belts use-at-own-risk version:

import datetime

def minimalist_xldate_as_datetime(xldate, datemode):
    # datemode: 0 for 1900-based, 1 for 1904-based
    return (
        datetime.datetime(1899, 12, 30)
        + datetime.timedelta(days=xldate + 1462 * datemode)
        )

Answer 5

A few lines of code (assuming you are using xlrd to read the spreadsheet)"

from datetime import date, datetime, timedelta

from1900to1970 = datetime(1970,1,1) - datetime(1900,1,1) + timedelta(days=2)

value = sheet.cell_value(row,col)

value = date.fromtimestamp( int(value) * 86400) - from1900to1970