29

I am reading data from excel and manipulating the data using python. But dates are coming as integers. How can I convert the dates back to date format?

5/15/2015 is coming as 42139.00

Mel
  • 5,837
  • 10
  • 37
  • 42
user2728024
  • 1,496
  • 8
  • 23
  • 39
  • for a `pandas`-specific solution, see also [Convert Excel style date with pandas](https://stackoverflow.com/q/38454403/10197418). – FObersteiner Dec 23 '21 at 11:04

2 Answers2

53
from datetime import datetime
excel_date = 42139
dt = datetime.fromordinal(datetime(1900, 1, 1).toordinal() + excel_date - 2)
tt = dt.timetuple()
print(dt)
print(tt)

As mentioned by J.F. Sebastian, this answer only works for any date after 1900/03/01

EDIT: (in answer to @R.K)

If your excel_date is a float number, use this code:

from datetime import datetime

def floatHourToTime(fh):
    hours, hourSeconds = divmod(fh, 1)
    minutes, seconds = divmod(hourSeconds * 60, 1)
    return (
        int(hours),
        int(minutes),
        int(seconds * 60),
    )

excel_date = 42139.23213
dt = datetime.fromordinal(datetime(1900, 1, 1).toordinal() + int(excel_date) - 2)
hour, minute, second = floatHourToTime(excel_date % 1)
dt = dt.replace(hour=hour, minute=minute, second=second)

print(dt)
assert str(dt) == "2015-05-15 00:13:55"
saeedgnu
  • 4,110
  • 2
  • 31
  • 48
  • 1
    it worked but date 42139 is coming as 2015-05-17 00:00:00 not as 2015-05-15 00:00:00 – user2728024 Jul 11 '15 at 16:32
  • yes. -2 solved it. But strange. – user2728024 Jul 11 '15 at 17:07
  • 4
    @user2728024: `-2` is not always correct, see [this answer](http://stackoverflow.com/a/29387450/4279) – jfs Jul 11 '15 at 18:50
  • I have a time in float format Eg. 42139.23213 .... This includes time as well.. but with datetime.fromordial() the output i get is something like dd/mm/yyyy 00:00:00.... My time is always 0hrs... how do i get correct time with date? @saeedgnu – R.K Mar 03 '18 at 10:06
  • @R.K I'll edit my answer – saeedgnu Mar 07 '18 at 07:00
  • 4
    `-2` because the "start date" for Excel dates is actually the last day of 1899, and Excel assumes that 1900 was a leap year (it's not). https://xlrd.readthedocs.io/en/latest/dates.html#dates-in-excel-spreadsheets – MinchinWeb Nov 26 '19 at 23:18
  • The first `divmod` call should be `divmod(24*fh, 1)`, rather than `divmod(fh, 1)`. – jinpan Feb 14 '23 at 23:48
  • my cent : if you can import xlrd, avoid all those excel messy algorithm and constants. Use xlrd.xldate_as_tuple(xl_value, 0) (2nd parameter is 0 for 1900 based excel dates, or 1 for 1904 based excel dates. In my experience, dates are generally 1900 based) – herve-guerin May 17 '23 at 18:50
22

The module xlrd provides a function xldate_as_tuple to convert Excel's numerical date format to a tuple (year, month, day, hour, minute, nearest_second).

You can then use datetime.datetime to convert the tuple into a datetime-object.

from datetime import datetime
import xlrd

excel_date = 44032
python_date = datetime(*xlrd.xldate_as_tuple(excel_date, 0))
gofvonx
  • 1,370
  • 10
  • 20
lispsil
  • 411
  • 3
  • 8
  • 2
    The second parameter of `xlrd.xldate_as_tuple()` is the workbook's "datemode" (i.e. 1900 or 1904-based dates). Better to pass it in directed from your workbook. (i.e. the `datemode` attribute of your workbook). https://xlrd.readthedocs.io/en/latest/dates.html#dates-in-excel-spreadsheets – MinchinWeb Nov 26 '19 at 23:37
  • xlrd.xldate_as_datetime(excel_date, 0) same as datetime(*xlrd.xldate_as_tuple(excel_date, 0)) – Lucas peret Jan 02 '22 at 11:45