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'])

2 Answers 2


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.

  • 1
    $\begingroup$ The second snippet worked perfectly, Didn't know that I could round-off a datetime object. Thanks! $\endgroup$
    – Uday T
    Commented Nov 11, 2019 at 8:35
  • 1
    $\begingroup$ Is there a way to do this automatically for all columns using datetimes (without specifying them one by one), when doing read_excel()? $\endgroup$
    – Basj
    Commented Mar 8, 2021 at 16:25
  • $\begingroup$ I don't know why, but when reading an xlsx file with pandas, some datetimes were off by a minute. So I had to use df.set_index(df.index.round(freq='h'), inplace=True), which seems really weird. I'd rather parse a CSV file and specify an explicit strftime format. $\endgroup$ Commented Jun 6 at 12:03

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


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.