Convert excel days to datetime
From raju
python
To convert a column of excel days in a dataframe
import pandas as pd def excel_days_to_datetime(days): epoch = pd.to_datetime('1899-12-30') delta = pd.to_timedelta(days, 'D') return epoch + delta
Usage:
df = pd.DataFrame({'id':['alpha', 'beta', 'gamma', 'delta'], 'days': [47347, 45552, 43725, 43694]}) print(df) id days 0 alpha 47347 1 beta 45552 2 gamma 43725 3 delta 43694 df['datetime'] = excel_days_to_datetime(df['days']) print(df) id days datetime 0 alpha 47347 2029-08-17 1 beta 45552 2024-09-17 2 gamma 43725 2019-09-17 3 delta 43694 2019-08-17
It will also work on a single value
print(excel_days_to_datetime(43694)) 2019-08-17 00:00:00
Solution without using pandas for a single value
from datetime import datetime, timedelta def excel_days_to_datetime_nopd(days): epoch = datetime(1899, 12, 30) delta = timedelta(days) return epoch + delta
Usage
print(excel_days_to_datetime_nopd(43694)) 2019-08-17 00:00:00
Notes :-
- The epoch is set to January 0, 1900 in excel in order to be compatible with Lotus 1-2-3 which had a bug and assumed 1900 is a leap year. This is why in Python we have to start counting days from 1899-12-30 For mote details, see
Code :-