pyExcelerator question - dates map to floats?

[John Machin]

skip at pobox.com wrote:
> skip> Doing a little date math I come up with a base date of
>     skip> approximately (though not quite) 1900-01-01:
>     ...
>
> Reading the code in BIFFRecords.py I saw this docstring:
>
>         This record specifies the base date for displaying date values. All
>         dates are stored as count of days past this base date. In
>         BIFF2-BIFF4 this record is part of the Calculation Settings Block.
>         In BIFF5-BIFF8 it is stored in the Workbook Globals Substream.
>
>         Record DATEMODE, BIFF2-BIFF8:
>
>         Offset  Size    Contents
>         0       2       0 = Base is 1899-Dec-31 (the cell = 1 represents 1900-Jan-01)
>                         1 = Base is 1904-Jan-01 (the cell = 1 represents 1904-Jan-02)
>
> Shifting my base date from 1900-01-01 to 1899-12-31 leaves me off by one.

You have found Microsoft's "What do you mean, 1900 was not a leap
year?" bug.

> I take it then that "count of days past this base date" must include that
> date.  It's too late for me to do any more poking around.  Does anyone know
> if I might get date floats which are offset from 1904-01-01 (I think that
> was the traditional Mac start-of-epoch) or are they normalized to all be
> offsets from 1899-12-31?
>
> I noticed that there are two items on the pyExcelerator to-do list.  Number
> one is "documentation".

Check out my "xlrd" package.
http://cheeseshop.python.org/pypi/xlrd/0.5.2

(1) It has docs, including an extensive rant about Excel dates which
will answer all of your above questions.

(2) It has an helper functions for converting between Excel dates and
tuples (which can then be converted into datetime, time, and mxDateTime
dates).

Python 2.4.3 (#69, Mar 29 2006, 17:35:34) [MSC v.1310 32 bit (Intel)]
on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import xlrd
>>> xlrd.xldate_as_tuple(38973.0, datemode=0)
(2006, 9, 13, 0, 0, 0)
>>>

(3) It tracks the "number formats" applied to floats, and automatically
classifies them as "number" and "date".

HTH,
John