Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

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

  • Last Updated : 06 Sep, 2021

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:

 Attention geek! Strengthen your foundations with the Python Programming Foundation Course and learn the basics.  

To begin with, your interview preparations Enhance your Data Structures concepts with the Python DS Course. And to begin with your Machine Learning Journey, join the Machine Learning - Basic Level Course

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



My Personal Notes arrow_drop_up
Recommended Articles
Page :