Excel – How to compare time between 2 columns in Excel

date timemicrosoft excel

I have 2 columns of date in my Excel spreadsheet, and I want to calculate the time (in sec) between the 2 dates.

Time                            Time1
2017-08-22 19:45:22.2327928     2017-08-22 19:45:20.9915171
2017-08-22 19:45:40.3645187     2017-08-22 19:45:21.4451237
2017-08-22 19:45:25.2337426     2017-08-22 19:45:24.3347192
2017-08-22 19:45:20.8958264     2017-08-22 19:45:27.1250265
2017-08-22 19:45:29.5987311     2017-08-22 19:45:27.9014672

I went thru this article, and I tried using these formulas:

=TEXT(D2-B2, "hh:mm:ss")
=TEXT(D2-B2, "yyyy-mm-dd hh:mm:ss")

But none of them works.

Can you please tell me how can I get the time difference between 2 dates in Excel?

Best Answer

This formula will work:

=(DATEVALUE(D2)+TIMEVALUE(D2)-(DATEVALUE(B2)+TIMEVALUE(B2)))*24*60*60

Real datetime values are stored internally in Excel as numbers. (More specifically, the date part is stored as the integer part of the number and the time part is stored as the fraction part.)

You can also store a representation of a datetime as a string. This is what your values actually are.

To get the difference between two of your datetimes you first need to convert them to numbers. This is what DATEVALUE(D2)+TIMEVALUE(D2) does to D2.

Then, after calculating the difference, you need to could convert it to seconds. Remembering that a datetime (and thus a difference between datetimes) is stored as a number where 1 is a whole day, multiplying the difference by 24*60*60 converts it to seconds.

Related Question