I record my rides on an Excel spreadsheet. On Saturday I rode 29.4 miles in 4 hours 18 mins. How can I set a formula to convert this to mph? If I enter the time as 4.18 Excel regards the minutes (18) as a fraction, but if I use the time formula 4:18 then the calculation comes up with silly answer.

Does anyone know how to set a formula to do the calculation?

convert time to a decimal, 4.18 treat as 4 and 18/60. either enter over two cells, so you have an hour cell and a minutes cell or you need to break down the number so it is treated as such.

you can then add 4 and 18/60 to give the timetaken.

Thanks, but that’s too complicated and needs too many cells/key strokes. I’ve managed to find a formula that I can programme into my spreadsheet that will carry out the calculation for me and all I have to do is enter the values into pre formatted cells. A little bit of work now then easy peasy ever after.

=QUOTIENT(B1,1)+(MOD(B1, 1)/0.6)/A1

Typed into C1 where A1 is miles and B1 is time in the form H.MM

I was sent :-

Format cell B1 as Time, B2 as Number and B3 as Number. Enter 4:18 into B1 and 29.2 into B2 and =B2/((B1-INT(B1))*24) into B3

Needs a bit of tweaking to get it working across the page.

Enter the miles in cell A1 formatted as number
Enter time in cell B1 in TIME format (i.e. 4:18). The time format stores the number as a decimal of a day, e.g. 12:00 is stored as 0.5, 12 hours = 0.5 days)

So enter formula in cell C1 formatted as a number =(A1/B1)/24.

Ta da.