Python - Convert excel serial date to datetime

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

Output:

2020-01-01
<class 'str'>

Example 2: Python program to convert excel serial number to DateTime

Output:

2020-01-01
<class 'datetime.date'>