Formatting cells to show elapsed time

valerieb

New Member
Joined
Sep 24, 2002
Messages
5
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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
Hi - welcome to the board

The format for elapsed time is [hh]:mm

Paddy
 

valerieb

New Member
Joined
Sep 24, 2002
Messages
5
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

New Member
Joined
Sep 24, 2002
Messages
5
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234

ADVERTISEMENT

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

Well-known Member
Joined
Feb 16, 2002
Messages
761
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

New Member
Joined
Sep 24, 2002
Messages
5

ADVERTISEMENT

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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

New Member
Joined
Sep 24, 2002
Messages
5
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
 

Forum statistics

Threads
1,143,617
Messages
5,719,740
Members
422,242
Latest member
hishamkhatri

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top