# Formatting cells to show elapsed time

#### valerieb

##### New Member
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.

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Hi - welcome to the board

The format for elapsed time is [hh]:mm

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.

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.

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.

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

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.

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).

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

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

Replies
2
Views
306
Replies
4
Views
389
Replies
10
Views
291
Replies
14
Views
884
Replies
0
Views
959

1,218,756
Messages
6,144,304
Members
450,536
Latest member
DavidRankin

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back