Convert numeric date representation (excel format) to python date and time, then split them into two separate data columns in pandas

Question

I am importing some data into a spreadsheet. It is in a data frame, but the date is in a numerical representation or format

41516.43

First, I want to convert it to a date and time object

date_val = 30/08/2013  10:13:26 AM

Then I would like to split date_val by date and time separately and put them in separate columns in my data frame (df)

| **original date** | **date**     | **time** | 
41516.43              30/08/2013      10:13:26 AM

Answer 1

Collection from another question :

In [11]: s = pd.Series([41516.43])

In [12]: from xlrd.xldate import xldate_as_tuple

In [13]: from datetime import datetime

In [14]: s.apply(lambda x: datetime(*xldate_as_tuple(x, 0)))
Out[14]:
0   2013-08-30 10:19:12
dtype: datetime64[ns]

Note: the supposedly slight difference is due to the rounding of the float that you gave.

:

In [21]: pd.Timestamp('1899-12-30') + (pd.offsets.Day(1).nanos * s).astype(np.timedelta64)
Out[21]:
0   2013-08-30 10:19:12
dtype: datetime64[ns]

, read_excel.