Convert serial number to date python

Question

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

Nội dung chính Show

How do I change my serial number to a date?
How do I convert a date in Excel to a date in python?
How do I convert numeric to date in python?
How do I convert text to date in python?

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

Read

Discuss

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

Read

Discuss

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

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:

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

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

How do I change my serial number to a date?

Below are the steps to do this:.

Select the cells that have the number that you want to convert into a date..

Click the 'Home' tab..

In the 'Number' group, click on the Number Formatting drop-down..

In the drop-down, select 'Long Date' or Short Date' option (based on what format would you want these numbers to be in).

How do I convert a date in Excel to a date in python?

xldate_as_datetime() function is used to convert excel date/time number to datetime. datetime object. Parameters: This function accepts two parameters that are illustrated below: xldate: This is the specified excel date that will converted into datetime.

How do I convert numeric to date in python?

You can use the fromtimestamp function from the datetime module to get a date from a UNIX timestamp. This function takes the timestamp as input and returns the datetime object corresponding to the timestamp.