xlrd "year is out of range" error, xldate_as_tuple
Question: I am trying to use the `xldate_as_tuple`function to covert a datetime and a
time value from two seperate cells in a spreadsheet into python datetime and
time. I intend to combine the currently seperate values into one python
datetime value for use later in my code.

I can get the date row values to convert but having trouble with the time
fields. I think its down to the format of the time cell in excel.

In my situation the time fields in the spreadsheet are in the following
format: 00/01/1900 16:47, or 00/01/1900 17:06. All I am interested in is the
time (i.e. not the 00/01/1900 bit). Thinking about it, the '00' bit of the
date is not a valid day of the month so I think thats what is calusing my
problems.

Thoughts appreciated as how best to get the time value. If
the`xldate_as_tuple`will just not work for my situation then should I consider
somehow getting the value in the cell as text and parsing it...

Cheers


Answer: `xldate_as_tuple` works as advertised, producing a (year, month, day, hour,
minute, second) tuple. The problem is with what you are doing with that tuple,
which in your case will have 0 for each of year, month, and day.

Short answer:

If you want a time value, call `datetime.time`, not `datetime.datetime`.

Long answer:

Open Excel. Into cell A1, type `16:47:00`. Copy that into B1 and C1. Format B1
with custom format `yyyy-mm-dd hh:mm:ss`. You should see `1900-01-00
16:47:00`. Into cell D1 type `=(16+47/60)/24`, then format C1 and D1 as number
with 8 decimal places. You should see `0.699305556` in both C1 and D1.

Fire up `xlrd` and try out `xlrd.xldate_as_tuple`:

    
    
    >>> import xlrd
    >>> b = xlrd.open_workbook('xlrd_time.xls')
    >>> s = b.sheet_by_index(0)
    >>> s.row_values(0)
    [0.6993055555555556, 0.6993055555555556, 0.6993055555555556, 0.6993055555555556]
    >>> [xlrd.xldate_as_tuple(t, 0) for t in s.row_values(0)]
    [(0, 0, 0, 16, 47, 0), (0, 0, 0, 16, 47, 0), (0, 0, 0, 16, 47, 0), (0, 0, 0, 16, 47, 0)]
    >>>
    

Getting your fraction-of-a-day into `datetime` module territory: Your data is
either a time-of-day or a duration ("timedelta"), so:

    
    
    >>> import datetime
    >>> t = (16 + 47/60.) / 24
    >>> t
    0.6993055555555556
    >>> datetime.timedelta(days=t)
    datetime.timedelta(0, 60420)
    >>> x = xlrd.xldate_as_tuple(t, 0)
    >>> x
    (0, 0, 0, 16, 47, 0)
    >>> datetime.time(*x[3:])
    datetime.time(16, 47)
    

Your data is not a "datetime"; attempting to make a datetime will fail as you
have found out:

    
    
    >>> datetime.datetime(*x)
    Traceback (most recent call last):
      File "<stdin>", line 1, in <module>
    ValueError: year is out of range
    

