from datetime import datetime excel_date = 42139 dt = datetime.fromordinal(datetime(1900, 1, 1).toordinal() + excel_date - 2) tt = dt.timetuple() print(dt) print(tt)
As mentioned by J.F. Sebastian, this answer only works for any date after 1900/03/01
EDIT: (in answer to @R.K)
If your excel_date is a float number, use this code:
from datetime import datetime def floatHourToTime(fh): hours, hourSeconds = divmod(fh, 1) minutes, seconds = divmod(hourSeconds * 60, 1) return ( int(hours), int(minutes), int(seconds * 60), ) excel_date = 42139.23213 dt = datetime.fromordinal(datetime(1900, 1, 1).toordinal() + int(excel_date) - 2) hour, minute, second = floatHourToTime(excel_date % 1) dt = dt.replace(hour=hour, minute=minute, second=second) print(dt) assert str(dt) == "2015-05-15 00:13:55"View Discussion
Improve Article
Save Article
View Discussion
Improve Article
Save Article
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
import xlrd
xl_date = 43831
datetime_date = xlrd.xldate_as_datetime(xl_date, 0)
date_object = datetime_date.date()
string_date = date_object.isoformat()
print(string_date)
print(type(string_date))
Output:
2020-01-01 <class 'str'>Example 2: Python program to convert excel serial number to DateTime
Python3
import xlrd
xl_date = 43831
datetime_date = xlrd.xldate_as_datetime(xl_date, 0)
date_object = datetime_date.date()
print(date_object)
print(type(date_object))
Output:
2020-01-01 <class 'datetime.date'>View Discussion
Improve Article
Save Article
View Discussion
Improve Article
Save Article
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. Thestrftime() 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
import datetime
current_datetime = datetime.datetime.now()
print(current_datetime.strftime('%x %X'))
Output:
08/23/21 15:15:53If 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
from datetime import date
def convert_date_to_excel_ordinal(day, month, year):
offset = 693594
current = date(year, month, day)
n = current.toordinal()
return (n - offset)
print(convert_date_to_excel_ordinal(2, 2, 2021))
Output:
44229Example: 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
from datetime import datetime
import datetime as dt
def excel_date(date1):
temp = dt.datetime(1899, 12, 30)
delta = date1 - temp
return float(delta.days) + (float(delta.seconds) / 86400)
print(excel_date(datetime(2021, 2, 4)))
Output:
44231.0