PDA

View Full Version : Time functions in Excel 2002



wooda2
17-08-2004, 10:41 PM
Hi all.......I am trying to get an average speed in Excel 2002. In column A have times in hh:mm:ss format and in column B I have a distance (usually a constant 6.6km). In column C I want to get the average speed in kph from the values in columns A & B but I try and try and keep getting error messages.
I would appreciate any help.
Many thanks, Mark

godfather
17-08-2004, 11:05 PM
The problem is likely to be that the "time" is not really interpreted as a defined period, but the "time of day" by Excel. That is very different to what you want.

I don't think you can easily use "time" in that Excel format.

Convert the time to a straight numerical form of seconds.

So 01:10:30 = 4230 seconds for 6.6 kM

= 1.560284 metres per second
=5.617021 km/h

(any errors in maths are mine!)

andrew93
18-08-2004, 12:03 AM
Hi wooda2

You can use the hh:mm:ss format to get what you want - you could convert the number into seconds per Godfathers suggestion or you could make an adjustment to your formula as follows:

Cell A1 = 01:10:30 (per Godfathers example 1 hour, 10 minutes & 30 seconds formatted as hh:mm:ss)
Cell B1 = 6.6 (km)
Cell C1 = =B1/(A1*24) - which also returns the result 5.617021 (no problems with your maths godfather)

A number formatted as hh:mm:ss is stored by Excel as a proporion of the day and 1 hour 10 minutes and 30 seconds is 0.0489583 of one day - this number can be converted back into hours by multiplying by 24 as per my example formula above. I'm not getting any error messages with this but if you still are let us know.

Andrew

wooda2
18-08-2004, 01:08 PM
To Godfather and andrew93

Thank you for your help. I've got it sorted thanks to it.
Cheers Mark.