Convert Time Duration to Seconds in Google Sheets

Posted by ads on Tuesday, 2023-12-26
Posted in [Software]

When working with time duration (aka: time span) in Google Sheets, it is sometimes necessary to know how many seconds are elapsed. After all, 0:42:23 is not very handy to calculate with, and neither is 0*3600+42*60+23. But 2543 seconds is easy. Now how to calculate this in a cell.

Let me give you a real-world example from a spreadsheet:

Spreadsheet with several different time and speed calculations
Spreadsheet with several different time and speed calculations

In this example I track the step width for several different activities. This helps me with a better overall accuracy. I enter the following values:

  • Date (not used here, but helps to find the activity in Garmin Connect)
  • Distance, in km
  • Time Duration, in hh:mm:ss
  • Number of steps during the activity (as shown by my watch)
  • Type of activity (used for aggregation)

And the sheet calculates the following columns:

  • Speed in km/h
  • Steps per meter
  • Step length
  • Steps per second
  • Steps per km
  • Time for 1k steps (at this speed)

One can imagine that there is a great deal of inaccuracy if I use rounded values everywhere. Hence every calculation in this sheet uses the original data entered into the columns, and doesn’t depend on other calculated values. Here we did walk around in the evening for a bit, and also stopped for buying dinner. Not a fast pace, hence the “strolling”. How does one transform the 20 minutes 27 seconds into a number of seconds: by using the =VALUE() function:

=VALUE(<cell>)

In order to calculate the speed/pace, I need the following formula:

=IF(ISBLANK(D20),"", ROUND(((B20*1000)/VALUE(C20*24*60*60))*3.6,3))

The IF() part is used to “just” show an empty result field if nothing is entered in the “Steps” field in D20 - otherwise this will be a division by zero later on.

Then the distance in B20 is transformed into meter (multiplied by 1000), and the time duration in C20 is transformed into seconds, using the VALUE() function. The pace is calculated, and rounded to 3 digits.

Here is the calculation for “Steps per Meter”:

=IF(ISBLANK(D20),"", ROUND(1/(B20*1000/D20), 3))

“Step length”:

=IF(ISBLANK(D20),"", ROUND(B20*1000/D20, 3))

“Steps per second”:

=IF(ISBLANK(D20),"", ROUND(D20/VALUE(C20*24*60*60),3))

“Steps per kilometer”:

=IF(ISBLANK(D20),"", ROUND((1/(B20*1000/D20))*1000, 0))

And finally “Predicted time for 1k steps”:

=IF(ISBLANK(D20),"", (VALUE(C20*24*60*60)*1000/D20)/86400)

All calculations use the field B20 to D20 directly.


Categories: [Software]

Share: