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?
2 Answers
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.
These are not time values, they are text. You can convert them to time values with
=DATEVALUE(LEFT(A2,10))+RIGHT(A2,16)
Copy down and across.
After that, you can subtract one value from the other.
