[python] xldate_as_tuple

Question

im not quite sure how to use the following function:

xldate_as_tuple

for the following data

xldate:39274.0 xldate:39839.0

could someone please give me an example on usage of the function for the data?

Answer 1

Use it as such:

number = 39274.0
book_datemode = my_book.datemode
year, month, day, hour, minute, second = xldate_as_tuple(number, book_datemode)

Answer 2

Quoth the documentation:

Dates in Excel spreadsheets

In reality, there are no such things. What you have are floating point numbers and pious hope. There are several problems with Excel dates:

(1) Dates are not stored as a separate data type; they are stored as floating point numbers and you have to rely on (a) the "number format" applied to them in Excel and/or (b) knowing which cells are supposed to have dates in them. This module helps with (a) by inspecting the format that has been applied to each number cell; if it appears to be a date format, the cell is classified as a date rather than a number. Feedback on this feature, especially from non-English-speaking locales, would be appreciated.

(2) Excel for Windows stores dates by default as the number of days (or fraction thereof) since 1899-12-31T00:00:00. Excel for Macintosh uses a default start date of 1904-01-01T00:00:00. The date system can be changed in Excel on a per-workbook basis (for example: Tools -> Options -> Calculation, tick the "1904 date system" box). This is of course a bad idea if there are already dates in the workbook. There is no good reason to change it even if there are no dates in the workbook. Which date system is in use is recorded in the workbook. A workbook transported from Windows to Macintosh (or vice versa) will work correctly with the host Excel. When using this module's xldate_as_tuple function to convert numbers from a workbook, you must use the datemode attribute of the Book object. If you guess, or make a judgement depending on where you believe the workbook was created, you run the risk of being 1462 days out of kilter.

Reference: http://support.microsoft.com/default.aspx?scid=KB;EN-US;q180162

(3) The Excel implementation of the Windows-default 1900-based date system works on the incorrect premise that 1900 was a leap year. It interprets the number 60 as meaning 1900-02-29, which is not a valid date. Consequently any number less than 61 is ambiguous. Example: is 59 the result of 1900-02-28 entered directly, or is it 1900-03-01 minus 2 days? The OpenOffice.org Calc program "corrects" the Microsoft problem; entering 1900-02-27 causes the number 59 to be stored. Save as an XLS file, then open the file with Excel -- you'll see 1900-02-28 displayed.

Reference: http://support.microsoft.com/default.aspx?scid=kb;en-us;214326

which I quote here because the answer to your question is likely to be wrong unless you take that into account.

So to put this into code would be something like:

import datetime
import xlrd

book = xlrd.open_workbook("myfile.xls")
sheet = book.sheet_by_index(0)
ms_date_number = sheet.cell(5, 19) # Wrong
ms_date_number = sheet.cell_value(5, 19) # Correct option 1
ms_date_number = sheet.cell(5, 19).value # Correct option 2
year, month, day, hour, minute, second = xldate_as_tuple(ms_date_number, 
    book.datemode)
py_date = datetime.datetime(year, month, day, hour, minute, nearest_second)

which gives you a Python datetime in py_date that you can do useful operations upon using the standard datetime module.

I've never used xlrd, and my example is completely made up, but if there is a myfile.xls and it really has a date number in cell F20, and you aren't too fussy about precision as noted above, this code should work.

Answer 3

The documentation of the function (minus the list of possible exceptions):

xldate_as_tuple(xldate, datemode) [#]

Convert an Excel number (presumed to represent a date, a datetime or a
time) into a tuple suitable for feeding to datetime or mx.DateTime
constructors.

xldate
    The Excel number
datemode
    0: 1900-based, 1: 1904-based.
    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. 
Returns:
    Gregorian (year, month, day, hour, minute, nearest_second).

As the author of xlrd, I'm interested in knowing how the documentation can be made better. Could you please answer these:

Did you read the general section on dates (quoted by @msw)?
Did you read the above specific documentation of the function?
Can you suggest any improvement in the documentation?
Did you actually try running the function, like this:

>>> import xlrd
>>> xlrd.xldate_as_tuple(39274.0, 0)
(2007, 7, 11, 0, 0, 0)
>>> xlrd.xldate_as_tuple(39274.0 - 1.0/60/60/24, 0)
(2007, 7, 10, 23, 59, 59)
>>>