How to convert a given ordinal number (from Excel) to a date
The offset in Excel is the number of days since 1900/01/01, with 1
being the first of January 1900, so add the number of days as a timedelta to 1899/12/31:
from datetime import datetime, timedelta
def from_excel_ordinal(ordinal: float, _epoch0=datetime(1899, 12, 31)) -> datetime:
if ordinal >= 60:
ordinal -= 1 # Excel leap year bug, 1900 is not a leap year!
return (_epoch0 + timedelta(days=ordinal)).replace(microsecond=0)
You have to adjust the ordinal by one day for any date after 1900/02/28; Excel has inherited a leap year bug from Lotus 1-2-3 and treats 1900 as a leap year. The code above returns datetime(1900, 2, 28, 0, 0)
for both 59
and 60
to correct for this, with fractional values in the range [59.0 - 61.0) all being a time between 00:00:00.0 and 23:59:59.999999 on that day.
The above also supports serials with a fraction to represent time, but since Excel doesn't support microseconds those are dropped.
Converting Date in excel with Ordinal Notation
You could try the following:
Formula in B1
:
=TEXT(A1,"[$-409]mmm d")&IFERROR(INDEX({"st","nd","rd"},ROUNDUP(MATCH(DAY(A1),{1,21,31,2,22,32,3,23},0)/3,0)),"th")&TEXT(A1," yyyy hh:mm AM/PM")
And if you want the commas in there as well:
Formula in C1
:
=SUBSTITUTE(TEXT(A1,"[$-409]mmm d, yyyy, hh:mm AM/PM"),",",IFERROR(INDEX({"st,","nd,","rd,"},ROUNDUP(MATCH(DAY(A1),{1,21,31,2,22,32,3,23},0)/3,0)),"th,"),1)
Mind you, this transform a data into text!
Convert date from excel in number format to date format python
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"
Convert Excel style date with pandas
OK I think the easiest thing is to construct a TimedeltaIndex
from the floats and add this to the scalar datetime for 1900,1,1
:
In [85]:
import datetime as dt
import pandas as pd
df = pd.DataFrame({'date':[42580.3333333333, 10023]})
df
Out[85]:
date
0 42580.333333
1 10023.000000
In [86]:
df['real_date'] = pd.TimedeltaIndex(df['date'], unit='d') + dt.datetime(1900,1,1)
df
Out[86]:
date real_date
0 42580.333333 2016-07-31 07:59:59.971200
1 10023.000000 1927-06-12 00:00:00.000000
OK it seems that excel is a bit weird with it's dates thanks @ayhan:
In [89]:
df['real_date'] = pd.TimedeltaIndex(df['date'], unit='d') + dt.datetime(1899, 12, 30)
df
Out[89]:
date real_date
0 42580.333333 2016-07-29 07:59:59.971200
1 10023.000000 1927-06-10 00:00:00.000000
See related: How to convert a python datetime.datetime to excel serial date number
Convert number in Excel time format to string equivalent in python
Update
It seems using the utcfromtimestamp
on the datetime
function is erroneous(Thanks to @FObersteiner for the comment). You should use fromtimestamp
instead. So you can use the below code instead of the code at the bottom. Note that, pytz
is deprecated. It seems that zoneinfo
can do pytz
's job.
from datetime import datetime
import pytz
tzHong = pytz.timezone('Hongkong')
tzGMT = pytz.timezone('Etc/GMT')
dateString = "1645704206000"
date = datetime.fromtimestamp(float(dateString)/1000, tz=tzGMT)
print("Date in Hongkong: " + date.astimezone(tz=tzHong).strftime("%m-%d-%y %I:%M"))
print("Date in GMT: " + date.strftime("%m-%d-%y %I:%M"))
Old Version
It looks like there is a problem in the question related to Excel's natural internal representation. But if you are interested in converting timestamps (with milliseconds) into a string, human-readable date, you can use datetime
module in python:
from datetime import datetime
import pytz
tzHong = pytz.timezone('Hongkong')
tzGMT = pytz.timezone('Etc/GMT')
dateString = "1645704206000"
date = datetime.utcfromtimestamp(float(dateString)/1000)
print("Date in Hongkong: " + date.astimezone(tz=tzHong).strftime("%m-%d-%y %I:%M"))
print("Date in GMT: " + date.astimezone(tz=tzGMT).strftime("%m-%d-%y %I:%M"))
Output
Date in Hongkong: 02-24-22 08:03
Date in GMT: 02-24-22 12:03
Also note that the date you are showing (2/24/22 12:03) is in the GMT timezone, not Hongkong. Both time zones are shown in the code above. Make sure which one works for your desired output. Also, the answer provided by this answer is in 12-hour clock format. If you are interested in 24-hour format, just change %I
in the code above with %H
.
How to convert a column with Excel Serial Dates and regular dates to a pandas datetime?
- All the dates can't be parsed in the same manner
- Load the dataframe
- Cast the
dates
column as astr
if it's not already. - Use Boolean Indexing to select different date types
- Assuming regular dates contain a
/
- Assuming Excel serial dates do not contain a
/
- Assuming regular dates contain a
- Fix each dataframe separately based on its datetime type
- Concat the dataframes back together.
import pandas as pd
from datetime import datetime
# load data
df = pd.DataFrame({'dates': ['09/01/2020', '05/15/1985', '06/07/2013', '33233', '26299', '29428']})
# display(df)
dates
0 09/01/2020
1 05/15/1985
2 06/07/2013
3 33233
4 26299
5 29428
# set the column type as a str if it isn't already
df.dates = df.dates.astype('str')
# create a date mask based on the string containing a /
date_mask = df.dates.str.contains('/')
# split the dates out for excel
df_excel = df[~date_mask].copy()
# split the regular dates out
df_reg = df[date_mask].copy()
# convert reg dates to datetime
df_reg.dates = pd.to_datetime(df_reg.dates)
# convert excel dates to datetime; the column needs to be cast as ints
df_excel.dates = pd.TimedeltaIndex(df_excel.dates.astype(int), unit='d') + datetime(1900, 1, 1)
# combine the dataframes
df = pd.concat([df_reg, df_excel])
display(df)
dates
0 2020-09-01
1 1985-05-15
2 2013-06-07
3 1990-12-28
4 1972-01-03
5 1980-07-28
Convert column in excel date format (DDDDD.tttt) to datetime using pandas
Given
# s = df['date']
s
0 42411.0
1 42754.0
Name: 0, dtype: float64
Convert from Excel to datetime using:
s_int = s.astype(int)
# Correcting Excel Leap Year bug.
days = pd.to_timedelta(np.where(s_int > 59, s_int - 1, s_int), unit='D')
secs = pd.to_timedelta(
((s - s_int) * 86400.0).round().astype(int), unit='s')
pd.to_datetime('1899/12/31') + days + secs
0 2016-02-11
1 2017-01-19
dtype: datetime64[ns]
Reference.
How to convert a python datetime.datetime to excel serial date number
It appears that the Excel "serial date" format is actually the number of days since 1900-01-00, with a fractional component that's a fraction of a day, based on http://www.cpearson.com/excel/datetime.htm. (I guess that date should actually be considered 1899-12-31, since there's no such thing as a 0th day of a month)
So, it seems like it should be:
def excel_date(date1):
temp = dt.datetime(1899, 12, 30) # Note, not 31st Dec but 30th!
delta = date1 - temp
return float(delta.days) + (float(delta.seconds) / 86400)
Related Topics
"Pythonic" Method to Parse a String of Comma-Separated Integers into a List of Integers
Google Colab: How to Read Data from My Google Drive
Getting Computer's Utc Offset in Python
Attributeerror: 'Tensor' Object Has No Attribute 'Numpy'
Unicodedecodeerror When Redirecting to File
Print a String as Hexadecimal Bytes
Execute a File with Arguments in Python Shell
How to Print One Character at a Time on One Line
Calculate Area of Polygon Given (X,Y) Coordinates
Do Python for Loops Work by Reference
What's the Difference Between %S and %D in Python String Formatting
Pandas/Python: Set Value of One Column Based on Value in Another Column
Inserting Line at Specified Position of a Text File
Sorting Text File by Using Python