0

I want to change the following format

2012-12-22
2012-12-24
2012-12-25

to the excel style format

44120
44121
44123

How can I convert the DateTime format to excel style in pandas?

mevvoy
  • 11
  • for completeness, the other way 'round can be found here: [Convert Excel style date with pandas](https://stackoverflow.com/questions/38454403/convert-excel-style-date-with-pandas). – FObersteiner May 12 '21 at 12:42
  • 1
    Does this answer your question? [Convert Excel style date with pandas](https://stackoverflow.com/questions/38454403/convert-excel-style-date-with-pandas) – William Prigol Lopes May 12 '21 at 13:31

1 Answers1

0

You can write your own converter, pure Python example:

from datetime import datetime, timezone

def toExcelSerialDate(dt, _origin=datetime(1899,12,30,tzinfo=timezone.utc)):
    """
    convert a datetime object to Excel serial date
    """
    return (dt-_origin).total_seconds()/86400 # output in days since origin

for s in ["2012-12-22", "2012-12-24", "2012-12-25"]:
    print(toExcelSerialDate(datetime.fromisoformat(s).replace(tzinfo=timezone.utc)))
    
# 41265.0
# 41267.0
# 41268.0

applied to a pandas df:

import pandas as pd

df = pd.DataFrame({'datetime': ["2012-12-22", "2012-12-24", "2012-12-25"]})

# make sure your column is of dtype datetime:
df['datetime'] = pd.to_datetime(df['datetime'])

# subtract origin and convert to days:
df['excelDate'] = (df['datetime']-pd.Timestamp("1899-12-30")).dt.total_seconds()/86400

# df['excelDate']
# 0    41265.0
# 1    41267.0
# 2    41268.0
# Name: excelDate, dtype: float64
FObersteiner
  • 22,500
  • 8
  • 42
  • 72
  • Thanks for the comment! I add the following code: df['excelDate'].astype(str). Then the output isn't what I expected. I was wondering that I can get str type of 'excelDate' to look like int type. – mevvoy May 12 '21 at 12:55
  • @mevvoy note that the Excel serial date's origin is actually "1899-12-31", so that "1900-01-01" would be day 1. Using the origin "1899-12-30" here is to account for a bug that Excel inherited from Lotus 123, where the year 1900 was considered a leap year (which in fact, it is not since not divisible by 400). – FObersteiner May 12 '21 at 12:59
  • @mevvoy: to make the column "look like int" (but actually being string dtype), use `df['excelDate'].astype(int).astype(str)`. Why ever you need that ^^ – FObersteiner May 12 '21 at 13:00