4

When I try to convert from number format to Date I'm not getting the same result what I get in Excel.

I need to convert a Number to date format and get the same result what I get in Excel.

For Example in Excel for the below Number I get the following:

Input - 42970.73819
Output- 8/23/2017 17:43

I tried using the date conversion in Pandas but not getting the same result as of Excel.

Thank you Madan

Abdelsalam Shahlol
  • 1,621
  • 1
  • 20
  • 31
Madan Kumar
  • 55
  • 1
  • 5

1 Answers1

8

I think you need convert serial date:

df = pd.DataFrame({'date':[42970.73819,42970.73819]})
print (df)
          date
0  42970.73819
1  42970.73819

df = pd.to_datetime((df['date'] - 25569) * 86400.0, unit='s')
print (df)
0   2017-08-23 17:42:59.616
1   2017-08-23 17:42:59.616
Name: date, dtype: datetime64[ns]
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • 4
    Yeah... the logic is in `xlrd.xldate_as_datetime` but there's two offsets for years 0 for Windows (1900) and 1 for Mac (1904)... so `df.date.apply(xlrd.xldate_as_datetime, args=(0,))` is also an option there... (assuming you're going to be working with Excel documents at one point with pandas one's likely to have xlrd, xlwt and openpyxl installed) – Jon Clements Sep 11 '17 at 11:52