Menu
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
252
xldate_as_datetime() function is used to convert excel date/time number to datetime. datetime object. Parameters: This function accepts two parameters that are illustrated below: xldate: This is the specified excel date that will converted into datetime.
You can also convert serial number to date with formula in Excel. Please do as follows. 1. Select a blank cell (says cell B2) adjacent to the serial number cell you need to convert to date, then enter formula =TEXT(A2,"m/d/yyyy") into the Formula Bar, and press the Enter key.
Use datetime. strftime(format) to convert a datetime object into a string as per the corresponding format . The format codes are standard directives for mentioning in which format you want to represent datetime. For example, the %d-%m-%Y %H:%M:%S codes convert date to dd-mm-yyyy hh:mm:ss format.
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)) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
