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

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'>

Python - Convert excel serial date to datetime

Explore