Excel and Time Handling

Topic

New Reply

Hello,

Every time I try to use times in Excel, it wants to make them into times of the day instead of just time. In this case I have a bunch of times related to a cycling time trial. Riders ride a 10.25 mile course and have a time to complete the ride in minutes and seconds. So I do not want to deal with hours at all. Ultimately I want to enter time and calculate speed. The calculation is 615 divided by the time. 615 is 10.25 times 60 to convert minutes to hours and 10.25 is the mileage of the course. Here are my issues:

First: I want to be able to enter data as 22:07 for 22 minutes and 7 seconds. If I enter data this way it will be entered as 22 hours and 7 minutes. The only way I can seem to get what I am looking for is to format the cells as [mm]:ss and enter the data as 0:22:07.

Second: if my time data is in column C and we are in row 5, here is my calculation for the speed.

=(615/((MINUTE(C5))+((SECOND(C5))/60)))

I probably have too many ()’s in there but I wanted to make sure I got it right Here are my questions on this one:

[*]Are there any issues with my formula? – it seems to work fine
[*]Is there an easier way to do the calculation?
[*]How can I handle data entry more easily? (entering an extra “0:” for every rider is a lot of extra work)

Thanks,

Andy

Reply | Quote

Replies

A simpler calc would be 10.25/C5/24

Reply | Quote

Geez Rory – that was kind of simple

That makes the calculation much easier. Any thoughts on the entry of the time without the preceding “0:”, so I can just enter minutes:seconds?

This group never ceases to amaze me with how simple things can be

Thanks!

Andy

Reply | Quote

If you enter it as 22:07, then you can simply make the formula:
=10.25/(C5/60)/24

If you enter it as 22.07, then you could use:
=10.25/((INT(C5)+(MOD(C5,1)*100)/60)/1440)/24

otherwise you would need code to alter the entry to minutes and seconds from hours and minutes.

Reply | Quote

OK, I am trying to implement the [=10.25/(C5/60)/24] formula and it works but when I enter the data, I get values such as:

22:07
22:37
23:35

but then, the ones greater than 24, change

24:37 appears as 0:37
25:57 appears as 0:57

if I look at the formula field near the top of Excel the upper values show as 10:07:00 PM and the ones over 24 show as 01/01/1900 12:37:00 AM.

The formatting for the cells is Custom h:mm in all cases.

I can not figure out how to get that date out of the value. I want to show both the times in min:sec, along with the mph values.

Thanks and sorry to be a pain…

Andy

Reply | Quote

Format the cells as [hh]:mm

Reply | Quote

Thanks for getting me unwrapped from the axle

Have a good one – that worked like a champ

Andy

Reply | Quote