# Pandas datetime error when reading from excel file

I am trying to read an excel file that has two columns using pandas.

This is how the data looks in excel file:

DT                    Values
2019-11-11 10:00      28.9
2019-11-11 10:01      56.25
2019-11-11 10:02      2.45
2019-11-11 10:03      96.3
2019-11-11 10:04      18.4
2019-11-11 10:05      78.9


This is how it looks when I read using pandas:

DT                         Values
2019-11-11 10:00:00.000    28.9
2019-11-11 10:01:00.000    56.25
2019-11-11 10:01:59:995    2.45
2019-11-11 10:02:59:995    96.3
2019-11-11 10:03:59:995    18.4
2019-11-11 10:04:59:995    78.9


I have tried creating a new DateTime column, putting the data in a new excel file, converting the DT column to DateTime format in both pandas and excel. Nothing has worked yet!

Why does this happen?

EDIT - 1

I already tried the following code but forgot to mention the snippet,

df= pd.read_excel('data.xlsx', parse_dates = ['DT'])

df['DT'] = pd.to_datetime(df['DT'])


Using pandas, first make sure you have a datetime column:

df['DT'] = pd.to_datetime(df['DT'])


To remove the milliseconds, a possible solution is to use round to obtain a specified frequency (in this case seconds).

df['DT'] = df['DT'].dt.round(freq='s')


Depending on the wanted final result, ceil (to always round up) or floor (always round down) could be more suitable.

• The second snippet worked perfectly, Didn't know that I could round-off a datetime object. Thanks! Nov 11 '19 at 8:35
• Is there a way to do this automatically for all columns using datetimes (without specifying them one by one), when doing read_excel()?
– Basj
Mar 8 '21 at 16:25

Too low of rank to comment. Could you check the data type of the 2019-11-11 10:00:00.000? Then look up how to convert type(obj) to date-time format. Maybe this will help Documentation