CODEWITHSUNDEEP
pythonpandasdatetimedataframe
Spedo
Spedo

Reputation: 365

Convert column in excel date format (DDDDD.tttt) to datetime using pandas

I have a dataframe with multiple columns and want to convert one of those columns which is a date of floats (excel date format - DDDDD.ttt) to datetime.

At the moment the value of the columns are like this:

42411.0
42754.0

So I want to convert them to:

2016-02-11
2017-01-19

Upvotes: 1

Views: 580

Answers (1)

cs95
cs95

Reputation: 402653

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.

Upvotes: 1

Related Questions