Convert date from excel in number format to date format python
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"
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))