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

Question

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

Answer 1

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:

Answer 2

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)