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:
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 toD2
.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.