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:
Dates are not stored as a separate data type; they are stored as floating point numbers and you have to rely on:
- the “number format” applied to them in Excel and/or
- knowing which cells are supposed to have dates in them.
This module helps with the former 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.
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
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 package’s
xldate_as_tuple()function to convert numbers from a workbook, you must use the
datemodeattribute of the
Bookobject. 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.
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. For example, is 59 the result of
1900-02-28entered directly, or is it
1900-03-01minus 2 days?
The OpenOffice.org Calc program “corrects” the Microsoft problem; entering
1900-02-27causes the number 59 to be stored. Save as an XLS file, then open the file with Excel and you’ll see
The Macintosh-default 1904-based date system counts
1904-01-02as day 1 and
1904-01-01as day zero. Thus any number such that
(0.0 <= number < 1.0)is ambiguous. Is 0.625 a time of day (
15:00:00), independent of the calendar, or should it be interpreted as an instant on a particular day (
The functions in
xldatetake the view that such a number is a calendar-independent time of day (like Python’s
datetime.timetype) for both date systems. This is consistent with more recent Microsoft documentation. For example, the help file for Excel 2002, which says that the first day in the 1904 date system is
Usage of the Excel
DATE()function may leave strange dates in a spreadsheet. Quoting the help file in respect of the 1900 date system:
If year is between 0 (zero) and 1899 (inclusive), Excel adds that value to 1900 to calculate the year. For example, DATE(108,1,2) returns January 2, 2008 (1900+108).
This gimmick, semi-defensible only for arguments up to 99 and only in the pre-Y2K-awareness era, means that
DATE(1899, 12, 31)is interpreted as
For further information, please refer to the documentation for the functions in