Python – Convert excel serial date to datetime

by

Let us see how to convert an excel serial date to a DateTime in Python.

The “serial date” format in Excel is actually the number of days since 1900-01-00, or January 1st, 1900. For example, the excel serial date number 38416 symbolizes March 5, 2005, and when converted to a DateTime value, it becomes 2005-03-05.

This can be accomplished by utilising the xlrd.xldate_as_datetime() method. To convert an excel date/time number to a datetime.datetime object, use the xlrd.xldate_as_datetime() function.

Syntax:

xldate_as_datetime (xldate, datemode)

Parameters:

xldate: This is the excel date given that will be converted to datetime.

datemode: This is the given datemode in which the conversion will take place.

Return Value: 

The datetime.datetime object is returned by this xldate_as_datetime  function.

To begin, use the function xlrd.xldate_as_datetime(date, 0) to convert the provided Excel date to a datetime.datetime object. Next call datetime.datetime.date() function on the returned datetime.datetime object to return the date as a datetime.date object.

Finally, call datetime.date.isoformat() to convert the returned datetime.date object to an ISO format date string.

Program to Convert Excel Serial Date to Datetime in Python

Example1: Converting excel serial date to string date in Python

Approach:

  • Importing xlrd module using the import keyword.
  • Take a variable and initialize it with an excel serial date.
  • Pass the above excel serial date, 0 as arguments to the xldate_as_datetime() function to convert the given excel serial date into datetime.datetime object.
  • Store it in a variable.
  • Convert the above datetime.datetime object into datetime.date object by calling the datetime_date.date() function.
  • Store it in another variable.
  • Apply isoformat() function on the above datetime.date object to convert it into the ISO format date string.
  • Print the above ISO format date string.
  • Print the type of the above ISO format date string using the type() function.
  • The Exit of the Program.

Below is the implementation:

# Importing xlrd module using the import keyword
import xlrd
  
# Take a variable and initialize it with an excel serial date
excel_date = 38416
  
# Pass the above excel serial date, 0 as arguments to the xldate_as_datetime() function 
# to convert the given excel serial date into datetime.datetime object
# Store it in a variable
datetime_obj = xlrd.xldate_as_datetime(excel_date, 0)
  
# Convert the above datetime.datetime object into datetime.date object 
# by calling the datetime_date.date() function.
# Store it in another variable
dateobj = datetime_obj.date()
  
# Apply isoformat() function on the above datetime.date object to convert the
# it into the ISO format date string
str_date = dateobj.isoformat()
  
# Print the above ISO format date string
print(str_date)
  
# Print the type of above ISO format date string using the type() function
print(type(str_date))

Output:

2005-03-05
<class 'str'>

Example2: Converting excel serial number to DateTime in Python

Approach:

  • Importing xlrd module using the import keyword.
  • Take a variable and initialize it with an excel serial date.
  • Pass the above excel serial date, 0 as arguments to the xldate_as_datetime() function to convert the given excel serial date into datetime.datetime object.
  • Store it in a variable.
  • Convert the above datetime.datetime object into datetime.date object by calling the datetime_date.date() function.
  • Store it in another variable.
  • Print the above date object(converted date).
  • Print the type of the above-obtained date.
  • The Exit of the Program.

Below is the implementation:

# Importing xlrd module using the import keyword
import xlrd
  
# Take a variable and initialize it with an excel serial date
excel_date = 38416
  
# Pass the above excel serial date, 0 as arguments to the xldate_as_datetime() function 
# to convert the given excel serial date into datetime.datetime object
# Store it in a variable
datetime_obj = xlrd.xldate_as_datetime(excel_date, 0)
  
# Convert the above datetime.datetime object into datetime.date object 
# by calling the datetime_date.date() function.
# Store it in another variable
dateobj = datetime_obj.date()
  
# Print the above date object(converted date)
print(dateobj)
  
# Print the type of the above obtained date 
print(type(dateobj))

Output:

2005-03-05
<class 'datetime.date'>

Leave a Comment