Simple Excel Question

[Barks]

I've got an excel spreadsheet of my running times over the past few months.

I've recorded all my times in the following cell format:

[hh]:mm

So we have:

1st August - 16:32
4th August - 17:03
5th August - 14:59

etc


The run distance is 3.86km and I'm trying to put in another field to calculate my speed. This simple equation is (of course) Speed = Distance / Time

However, I simply cannot get Excel to convert the ([hh]:mm) field into a decimal number. For example, 14:59 would go to:

(59/60) = 0.983
Therefore 14.983/60 = 0.2497

Thus speed = 3.86/0.2497 = 15.457km/h

But excel gives the final value, completely incorrect, in the same [hh]:mm format.

Any ideas how I do this in Excel or even that maths for a more accurate straight ([hh]:mm) to decimal time value method?

Many thanks

 

[Barks]

The problem is that Excel convert's the time to it's own number format when you enter it - for example 14:59 becomes 0.624305555555556.

Format all times as 'Text' and then try the formula below:

=3.86/((LEFT([I][B]{cell with run time}[/B][/I],2)+(RIGHT([I][B]{cell with run time}[/B][/I],2)/60))/60)

Thanks mate. Funnily enough I tried that exact formula earlier but it didn't work. Obviously changing the format to 'text' did the trick.

Thanks!