Open In App

How to convert a Python datetime.datetime to excel serial date number

Improve
Improve
Like Article
Like
Save
Share
Report

This article will discuss the conversion of a python datetime.datetime to an excel serial date number. The Excel “serial date” format is actually the number of days since 1900-01-00. The strftime() function is used to convert date and time objects to their string representation. It takes one or more inputs of formatted code and returns the string representation.

Syntax:

strftime(format)

Parameters: This function accepts a parameter which is illustrated below:

  • format: This is the specified format code in which the given date and time object is going to be represented.

Return values: It returns the string representation of the date or time object.

Example 1: In the example below, the current date and time are being converted into the excel serial date number. And the returned output will be in the format of ’08/23/21 15:15:53′ which is accepted by Excel as a valid date/time and allows for sorting in Excel.

Python3




# Python3 code to illustrate the conversion of
# datetime.datetime to excel serial date number
 
# Importing datetime module
import datetime
 
# Calling the now() function to return
# current date and time
current_datetime = datetime.datetime.now()
 
# Calling the strftime() function to convert
# the above current datetime into excel serial date number
print(current_datetime.strftime('%x %X'))


Output:

08/23/21 15:15:53

If we need the excel serial date number in the form of a date value, then this can be done using the toordinal() function.

Example 2: Serial number in a form of a date value

Python3




# Python3 code to illustrate the conversion of
# datetime.datetime to excel serial date number
 
# Importing date module from datetime
from datetime import date
 
# Taking the parameter from the calling function
def convert_date_to_excel_ordinal(day, month, year):
 
    # Specifying offset value i.e.,
    # the date value for the date of 1900-01-00
    offset = 693594
    current = date(year, month, day)
 
    # Calling the toordinal() function to get
    # the excel serial date number in the form
    # of date values
    n = current.toordinal()
    return (n - offset)
 
# Calculating the excel serial date number
# for the date "02-02-2021" by calling the
# user defined function convert_date_to_excel_ordinal()
print(convert_date_to_excel_ordinal(2, 2, 2021))


Output:

44229

Example: In the below example, the “2021-05-04” date is being converted into the excel serial date number with reference to the 1899-12-30 date.

Python3




# Python3 code to illustrate the conversion of
# datetime.datetime to excel serial date number
 
# Importing datetime module
from datetime import datetime
import datetime as dt
 
 
def excel_date(date1):
 
    # Initializing a reference date
    # Note that here date is not 31st Dec but 30th!
    temp = dt.datetime(1899, 12, 30)
    delta = date1 - temp
    return float(delta.days) + (float(delta.seconds) / 86400)
 
 
# Calculating the excel serial date number
# for the date "2021-05-04" by calling the
# user defined function excel_date()
print(excel_date(datetime(2021, 2, 4)))


Output:

44231.0


Last Updated : 06 Sep, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads