4

I am trying to read dates/time off an excel sheet using Python, but I only want to read in the time so I can perform computations on it. For example if I have a date in the format: 3/11/2003 4:03:00 AM on my excel sheet how can I read in just the 4:03:00 in Python? I ultimately want to be able to subtract hours, mins, or seconds from the read in time.

alecxe
  • 462,703
  • 120
  • 1,088
  • 1,195
terence vaughn
  • 521
  • 2
  • 11
  • 23

1 Answers1

8

The best option would be to convert the date to datetime using xldate_as_tuple. Assuming you have an test.xls file with 3/11/2003 4:03:00 AM in the A1 cell:

from datetime import datetime, timedelta
import xlrd

book = xlrd.open_workbook(filename='test.xls')
sheet = book.sheet_by_name('Sheet1')

date = sheet.cell_value(0, 0)
datetime_value = datetime(*xlrd.xldate_as_tuple(date, 0))

print datetime_value  # prints 2003-11-03 04:03:00
print datetime_value.time()  # 04:03:00
print datetime_value - timedelta(hours=1)  # prints 2003-11-03 03:03:00

Hope that helps.

alecxe
  • 462,703
  • 120
  • 1,088
  • 1,195
  • Ok so I have read in the date and time from the excel sheet and stored in a variable called date, but when `print date` in python it outputs `37691.16875`. Im not sure what format this is but after doing some searching this is how dates from excel sheets are read into python. SO I guess my real question is how to convert `37691.16875` in to `3/11/2003 4:03:00 AM` – terence vaughn Jul 16 '13 at 20:04
  • Ive tried that as well but I get an error: `Traceback (most recent call last): File "Data Cleaner.py", line 104, in Rule_2(sheet) File "Data Cleaner.py", line 31, in Rule_2 date = xlrd.xldate_as_tuple(c_val, book.datemode) File "/Library/Python/2.7/site-packages/xlrd/xldate.py", line 61, in xldate_as_tuple xldays = int(xldate) ValueError: invalid literal for int() with base 10: ''` any ideas? – terence vaughn Jul 16 '13 at 20:40
  • You were right. my problem was that there were some blank fields in some of the cells and thats why my program was crashing. Thanks for the help!!! – terence vaughn Jul 16 '13 at 21:06