python pandas incorrectly reading excel dates

pandas, python

Solution

I encountered the same issue and got around it by not parsing the dates using Pandas, but rather applying my own function (shown below) to the relevant column(s) of the dataframe:

def ExcelDateToDateTime(xlDate):
    epoch = dt.datetime(1899, 12, 30)
    delta = dt.timedelta(hours = round(xlDate*24))
    return epoch + delta

df = pd.DataFrame.from_csv('path')

df['Date'] = df['Date'].apply(ExcelDateToDateTime)

Note: This will ignore any time granularity below the hour level, but that's all I need, and it looks from your example that this could be the case for you too.

Problem

I have an excel file with dates formatted as such: ``` 22.10.07 16:00 22.10.07 17:00 22.10.07 18:00 22.10.07 19:00 ``` After using the parse method of pandas to read the data, the dates are read almost correctly: ``` In [55]: nts.data['Tid'][10000:10005] Out[55]: 10000 2007-10-22 15:59:59.997905 10001 2007-10-22 16:59:59.997904 10002 2007-10-22 17:59:59.997904 10003 2007-10-22 18:59:59.997904 ``` What do I need to do to either a) get it to work correctly, or b) is there a trick to fix this easily? (e.g. some kind of 'round' function for datetime)

Original source