This article will discuss the conversion of an excel serial date to DateTime in Python.
The Excel “serial date” format is actually the number of days since 1900-01-00 i.e., January 1st, 1900. For example, the excel serial date number 43831 represents January 1st, 2020, and after converting 43831 to a DateTime becomes 2020-01-01.
By using xlrd.xldate_as_datetime() function this can be achieved. The xlrd.xldate_as_datetime() function is used to convert excel date/time number to datetime.datetime object.
Syntax: xldate_as_datetime (xldate, datemode)
Parameters: This function accepts two parameters that are illustrated below:
- xldate: This is the specified excel date that will converted into datetime.
- datemode: This is the specified datemode in which conversion will be performed.
Return values: This function returns the datetime.datetime object.
First, call xlrd.xldate_as_datetime(date, 0) function to convert the specified Excel date to a datetime.datetime object. Then, call datetime.datetime.date() function on the returned datetime.datetime object to return the date as a datetime.date object. Lastly, call datetime.date.isoformat() function to convert the returned datetime.date object to a ISO format date string.
Let’s see some examples to illustrate the above algorithm:
Example: Python program to convert excel serial date to string date
Python3
# Python3 code to illustrate the conversion # of excel serial date to datetime # Importing xlrd module import xlrd # Initializing an excel serial date xl_date = 43831 # Calling the xldate_as_datetime() function to # convert the specified excel serial date into # datetime.datetime object datetime_date = xlrd.xldate_as_datetime(xl_date, 0) # Calling the datetime_date.date() function to convert # the above returned datetime.datetime object into # datetime.date object date_object = datetime_date.date() # Calling the isoformat() function to convert the # above returned datetime.date object into the # ISO format date string string_date = date_object.isoformat() # Getting the converted date string as output print(string_date) # Getting the type of returned date format print(type(string_date)) |
Output:
2020-01-01 <class 'str'>
Example 2: Python program to convert excel serial number to DateTime
Python3
# Python3 code to illustrate the conversion # of excel serial date to datetime # Importing xlrd module import xlrd # Initializing an excel serial date xl_date = 43831 # Calling the xldate_as_datetime() function to # convert the specified excel serial date into # datetime.datetime object datetime_date = xlrd.xldate_as_datetime(xl_date, 0) # Calling the datetime_date.date() function to convert # the above returned datetime.datetime object into # datetime.date object date_object = datetime_date.date() # Getting the converted date date as output print(date_object) # Getting the type of returned date format print(type(date_object)) |
Output:
2020-01-01 <class 'datetime.date'>
