# Using duration in a calculation H:mm:ss to decimal

1. ## Using duration in a calculation H:mm:ss to decimal

Hi

I am trying to produce a calculation for dividing distance (in km) by the time it takes to run (in the [h]:mm:ss format), to work out the average speed in km/h. The use of the duration format creates a problem in the usual calculation (dist/dur)*60.

I need to keep the duration format to keep the column subtotals.

Eg.

It takes 14 min and 23 sec to jog 2km. (2/14.23)*60 = average speed of 8.43km/h.

With the format as described above (ie (2/0:14:23)*60 = 12013.90 in Number format, which is clearly rubbish.

Can anyone please advise me on how I can get the info I need?  Register To Reply

2. ## Re: Using duration in a calculation

Hi and welcome to the board

If you enter 0:14:23 in cell A1 as time and distance in B1 your speed is given by =(B1/(A1*1440))*60 .
You can of course simplify the function, I left it so you can see how to convert time to decimal time ( *1440)  Register To Reply