How do I read a date in Excel format in Python?

Question

How can I convert an Excel date (in a number format) to a proper date in Python?

Answer 1

You can use xlrd.

From its documentation, you can read that dates are always stored as numbers; however, you can use xldate_as_tuple to convert it to a python date.

Note: the version on the PyPI seems more up-to-date than the one available on xlrd's website.

Answer 2

Here's the bare-knuckle no-seat-belts use-at-own-risk version:

import datetime

def minimalist_xldate_as_datetime(xldate, datemode):
    # datemode: 0 for 1900-based, 1 for 1904-based
    return (
        datetime.datetime(1899, 12, 30)
        + datetime.timedelta(days=xldate + 1462 * datemode)
        )

Answer 3

After testing and a few days wait for feedback, I'll svn-commit the following whole new function in xlrd's xldate module ... note that it won't be available to the diehards still running Python 2.1 or 2.2.

##
# Convert an Excel number (presumed to represent a date, a datetime or a time) into
# a Python datetime.datetime
# @param xldate The Excel number
# @param datemode 0: 1900-based, 1: 1904-based.
# <br>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.
# @return a datetime.datetime object, to the nearest_second.
# <br>Special case: if 0.0 <= xldate < 1.0, it is assumed to represent a time;
# a datetime.time object will be returned.
# <br>Note: 1904-01-01 is not regarded as a valid date in the datemode 1 system; its "serial number"
# is zero.
# @throws XLDateNegative xldate < 0.00
# @throws XLDateAmbiguous The 1900 leap-year problem (datemode == 0 and 1.0 <= xldate < 61.0)
# @throws XLDateTooLarge Gregorian year 10000 or later
# @throws XLDateBadDatemode datemode arg is neither 0 nor 1
# @throws XLDateError Covers the 4 specific errors

def xldate_as_datetime(xldate, datemode):
    if datemode not in (0, 1):
        raise XLDateBadDatemode(datemode)
    if xldate == 0.00:
        return datetime.time(0, 0, 0)
    if xldate < 0.00:
        raise XLDateNegative(xldate)
    xldays = int(xldate)
    frac = xldate - xldays
    seconds = int(round(frac * 86400.0))
    assert 0 <= seconds <= 86400
    if seconds == 86400:
        seconds = 0
        xldays += 1
    if xldays >= _XLDAYS_TOO_LARGE[datemode]:
        raise XLDateTooLarge(xldate)

    if xldays == 0:
        # second = seconds % 60; minutes = seconds // 60
        minutes, second = divmod(seconds, 60)
        # minute = minutes % 60; hour    = minutes // 60
        hour, minute = divmod(minutes, 60)
        return datetime.time(hour, minute, second)

    if xldays < 61 and datemode == 0:
        raise XLDateAmbiguous(xldate)

    return (
        datetime.datetime.fromordinal(xldays + 693594 + 1462 * datemode)
        + datetime.timedelta(seconds=seconds)
        )

Answer 4

xlrd.xldate_as_tuple is nice, but there's xlrd.xldate.xldate_as_datetime that converts to datetime as well.

import xlrd
wb = xlrd.open_workbook(filename)
xlrd.xldate.xldate_as_datetime(41889, wb.datemode)
=> datetime.datetime(2014, 9, 7, 0, 0)

Answer 5

Please refer to this link: Reading date as a string not float from excel using python xlrd

it worked for me:

in shot this the link has:

import datetime, xlrd
book = xlrd.open_workbook("myfile.xls")
sh = book.sheet_by_index(0)
a1 = sh.cell_value(rowx=0, colx=0)
a1_as_datetime = datetime.datetime(*xlrd.xldate_as_tuple(a1, book.datemode))
print 'datetime: %s' % a1_as_datetime

Answer 6

Incase you're using pandas and your read_excel reads in Date formatted as Excel numbers improperly and need to recover the real dates behind...

The lambda function applied on the column uses xlrd to recover the date back

import xlrd
df['possible_intdate'] = df['possible_intdate'].apply(lambda s: xlrd.xldate.xldate_as_datetime(s, 0))


>> df['possible_intdate']

   dtype('<M8[ns]')

Answer 7

Expected situation

# Wrong output from cell_values()
42884.0

# Expected output
2017-5-29

Example: Let cell_values(2,2) from sheet number 0 will be the date targeted

Get the required variables as the following

workbook = xlrd.open_workbook("target.xlsx")
 
sheet = workbook.sheet_by_index(0)

wrongValue = sheet.cell_value(2,2)

And make use of xldate_as_tuple

year, month, day, hour, minutes, seconds = xlrd.xldate_as_tuple(wrongValue, workbook.datemode)
print("{0} - {1} - {2}".format(year, month, day))

That's my solution

Answer 8

Since there's a chance that your excel files are coming from different computers/people; there's a chance that the formatting is messy; so be extra cautious.

I just imported data from 50 odd excels where the dates were entered in DD/MM/YYYY or DD-MM-YYYY, but most of the Excel files stored them as MM/DD/YYYY (Probably because the PCs were setup with en-us instead of en-gb or en-in).

Even more irritating was the fact that dates above 13/MM/YYYY were in DD/MM/YYYY format still. So there was variations within the Excel files.

The most reliable solution I figured out was to manually set the Date column on each excel file to to be Plain Text -- then use this code to parse it:

if date_str_from_excel:
    try:
        return datetime.strptime(date_str_from_excel, '%d/%m/%Y')
    except ValueError:
        print("Unable to parse date")

Answer 9

excel stores dates and times as a number representing the number of days since 1900-Jan-0, if you want to get the dates in date format using python, just subtract 2 days from the days column, as shown below:

Date = sheet.cell(1,0).value-2 //in python

at column 1 in my excel, i have my date and above command giving me date values minus 2 days, which is same as date present in my excel sheet

Answer 10

This is a revised version from @hounded. My code handles both date and time, something like 43705.591795706

    import math
    import datetime


    def xldate_to_datetime(xldatetime): #something like 43705.6158241088

      tempDate = datetime.datetime(1899, 12, 31)
      (days, portion) = math.modf(xldatetime)

      deltaDays = datetime.timedelta(days=days)
      #changing the variable name in the edit
      secs = int(24 * 60 * 60 * portion)
      detlaSeconds = datetime.timedelta(seconds=secs)
      TheTime = (tempDate + deltaDays + detlaSeconds )
      return TheTime.strftime("%Y-%m-%d %H:%M:%S")


xldate_to_datetime(43705.6158241088)
# 2019-08-29 14:46:47

Answer 11

For quick and dirty:

year, month, day, hour, minute, second = xlrd.xldate_as_tuple(excelDate, wb.datemode)
whatYouWant = str(month)+'/'+str(day)+'/'+str(year)

Answer 12

A combination of peoples post gave me the date and the time for excel conversion. I did return it as a string

def xldate_to_datetime(xldate):
  tempDate = datetime.datetime(1900, 1, 1)
  deltaDays = datetime.timedelta(days=int(xldate))
  secs = (int((xldate%1)*86400)-60)
  detlaSeconds = datetime.timedelta(seconds=secs)
  TheTime = (tempDate + deltaDays + detlaSeconds )
  return TheTime.strftime("%Y-%m-%d %H:%M:%S")

Answer 13

If you have a datetime column in excel file. Then below code will fix it. I went through a lot of answers on StackOverflow and nothing fixed it. I thought file is corrupted.

from datetime import datetime
jsts = 1468629431.0
datetime.fromtimestamp(jsts) 

Answer 14

When converting an excel file to CSV the date/time cell looks like this:

foo, 3/16/2016 10:38, bar,

To convert the datetime text value to datetime python object do this:

from datetime import datetime

date_object = datetime.strptime('3/16/2016 10:38', '%m/%d/%Y %H:%M')    # excel format (CSV file)

print date_object will return 2005-06-01 13:33:00