Convert Excel style date with pandas

by

OK I think the easiest thing is to construct a TimedeltaIndex from the floats and add this to the scalar datetime for 1900,1,1:

In [85]:
import datetime as dt
import pandas as pd
df = pd.DataFrame({'date':[42580.3333333333, 10023]})
df

Out[85]:
           date
0  42580.333333
1  10023.000000

In [86]:
df['real_date'] = pd.TimedeltaIndex(df['date'], unit="d") + dt.datetime(1900,1,1)
df

Out[86]:
           date                  real_date
0  42580.333333 2016-07-31 07:59:59.971200
1  10023.000000 1927-06-12 00:00:00.000000

OK it seems that excel is a bit weird with it’s dates thanks @ayhan:

In [89]:
df['real_date'] = pd.TimedeltaIndex(df['date'], unit="d") + dt.datetime(1899, 12, 30)
df

Out[89]:
           date                  real_date
0  42580.333333 2016-07-29 07:59:59.971200
1  10023.000000 1927-06-10 00:00:00.000000

See related: How to convert a python datetime.datetime to excel serial date number

More Related Contents:

  1. Find the end of the month of a Pandas DataFrame Series
  2. Splitting timestamp column into separate date and time columns
  3. Leave dates as strings using read_excel function from pandas in python
  4. Which is the fastest way to extract day, month and year from a given date?
  5. Clean way to convert quarterly periods to datetime in pandas
  6. How to combine year, month, and day columns to single datetime column?
  7. How do I convert timestamp to datetime.date in pandas dataframe?
  8. Convert dates to pd.to_datetime where month could be either a number or month name
  9. Append existing excel sheet with new dataframe using python pandas
  10. Using Pandas to pd.read_excel() for multiple worksheets of the same workbook
  11. Filtering Pandas DataFrames on dates
  12. How to print pandas DataFrame without index
  13. How to convert string to datetime format in pandas python?
  14. How to format date string via multiple formats in python
  15. How do I read a date in Excel format in Python?
  16. Pandas: Looking up the list of sheets in an excel file
  17. Pandas: Subtracting two date columns and the result being an integer
  18. Python Pandas: Group datetime column into hour and minute aggregations
  19. Matplotlib DateFormatter for axis label not working
  20. Extract day of year and Julian day from a string date
  21. Comparing two date strings in Python
  22. Get week start date (Monday) from a date column in Python (pandas)?
  23. drop_duplicates not working in pandas?
  24. Pandas: Datetime Improperly selecting day as month from date [duplicate]
  25. Python pandas convert datetime to timestamp effectively through dt accessor
  26. Locate first and last non NaN values in a Pandas DataFrame
  27. Python Pandas – Read csv file containing multiple tables
  28. Pandas custom function to find whether it is the 1st, 2nd etc Monday, Tuesday, etc – all suggestions welcome
  29. pandas extract year from datetime: df[‘year’] = df[‘date’].year is not working
  30. Python Pandas : pandas.to_datetime() is switching day & month when day is less than 13

Leave a Comment