Forum Home
Press F1
Thread ID: 48227	2004-08-17 10:41:00	Time functions in Excel 2002	wooda2 (4837)	Press F1

Post ID	Timestamp	Content	User
262580	2004-08-17 10:41:00	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	wooda2 (4837)
262581	2004-08-17 11:05:00	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!)	godfather (25)
262582	2004-08-17 12:03:00	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	andrew93 (249)
262583	2004-08-18 01:08:00	To Godfather and andrew93 Thank you for your help. I've got it sorted thanks to it. Cheers Mark.	wooda2 (4837)
1