How to calculate time difference in seconds between two cells, where cells are formatted as text in format "yyyy-mm-dd hh:mm:ss"?

0
votes

How to calculate time difference in seconds between two text cells, where cells are formatted in format "yyyy-mm-dd hh:mm:ss"?

Thanks a million guys!

2019-03-21 09:53:56
2019-03-21 09:54:25
2019-03-21 14:10:36
2019-03-21 14:10:42
2019-03-22 11:49:16
2019-03-22 11:49:51
2019-03-22 11:50:21
2019-03-22 11:50:50
2
excelexcel-formulaexcel-2003
Suggest you to format the cells as Date/Time format and then use excel built-in date/time functions. – GCSDC
If you find the answers useful, then thank you is expressed by you voting them up... You can use the tick to accept one of them - helps the site keep track of answered questions. All this info is in the faqs available. – Solar Mike

2 Answers

0
votes

When you subtract two datetimes in excel you will get back the number of days as a decimal down to the accuracy of a second.

So 2019-03-21 09:53:56 - 2019-03-21 09:54:25 will give you: 0.000335648 as a return. You can multiply that time the number of seconds in a day to get your answer 0.000335648*24*60*60 = 29 seconds.

In a formula this would be =(A2-A1)*24*60*60:

enter image description here

0
votes

If your date-times are entered as text, you can use something like this to convert them into values Excel can use for calculations:

=DATEVALUE(LEFT(A1,10)) + TIMEVALUE(RIGHT(A1,8))

To convert a difference between two such values to seconds, multiply by 86400 (24*60*60)