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:

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.

Share: