Formatting cells to show elapsed time

[valerieb]

Hi,

I'm new to these boards and am hoping you can help me. I've set up a workbook in which my husband can track/analyze pace (minutes/seconds per mile) for the cross country team he coaches. The problem is that I can't figure out how to get the cells to format to show the elapsed time (e.g. 7 minutes 32 seconds). I keep getting time (e.g. 7:32 pm) accompanied by a date. I've tried all different formats - nothing seems to work. Is there a way to enter elapsed time without the hours, the "am/pm" and the date? My fall-back plan is to convert seconds into the equivalent fraction of a minute, and use decimals - but that's a lot of calculation.

Any help would be greatly appreciated.

Thanks.

 

[PaddyD]

Hi - welcome to the board

The format for elapsed time is [hh]:mm

Paddy

 

[valerieb]

Thanks for your quick response.

Will that give me hours and minutes, or minutes and seconds? (I need the latter.)I've tried [mm]:ss and still get the data to convert to other values.

 

[valerieb]

Thanks for your quick response.

Will that give me hours and minutes, or minutes and seconds? (I need the latter.)I've tried [mm]:ss and still get the data to convert to other values.

 

[PaddyD]

I take it you have a calculation in a cell that is calculating elapsed time based on data in other cells - post back with some egs of the data & the calculation.

Paddy

 

[RichardS]

Try formatting as h:mm:ss, but when you enter the time 7 minutes 32 seconds, enter
0:7:32.

Does that get you there?

Richard

 

[valerieb]

PaddyD,

I'm just entering straiht time data, no calculations - the coaches record elaped time at each mile - we're looking at 3.1 miles per race, so what I have is the length of time it took a runner to complete each of 3 miles (in minutes/seconds), then finish time (which is slightly longer than the sum of the 3 interim times).

Then I'm creating line charts for each runner that analyze the trend in their interim times over several races, the trend in their finish times.

Example data:
Runner #1, race of 8/26/02:
Mile 1 time: 7:32
Mile 2 time: 8:34
Mile 3 time: 8.40
Finish time: 25.35

RichardS:
I've tried your suggested format and it may work - the format displays correctly in the cell. I've got one uncertainty still: in the formula bar, the data still show up as clock times - e.g. 0:7:32 shows up as 12:07:32 a.m. (That's actually an improvement.With some of the other formats, I was getting dates as well - around January, 1902.)
Will the fact that the formula shows this as a clock time affect my line graphs, do you think? (I may just have to do one runner completely with this format, and see if it works.)

Thanks again.

 

[PaddyD]

As you sems to be moving towards a solution, I'll just post a few links that might give you a bit more info on how excel stores dates / times (important, cos it's not necessarily obvious...). First check out the excel help for "how excel stores data & times' (or the equivalent).

See also:

http://www.ozgrid.com/Excel
& scroll down the introduction page to the 'Date & times' page

http://www.mvps.org/dmcritchie/excel/datetime.htm
for all sorts of stuff on date / time calculations

Paddy

 

[valerieb]

PaddyD and Richard S: Thanks.

Using the h:mm:ss format seems to be working - I've applied that format to part of one runner's data and ran the chart - and it looks right.

I read through Excel's program help on date/time storage before I found this web site, but it didn't seem to address this specific question. I'll check out the other links - I'd like to be sure my charts won't be skewed.

I think I've got what I need for now - thank you both very much.

I found these boards by searching for "Excel Help". What a wonderful resource. Thanks again.

ValerieB