Excel 2007 time format headache

[asayc423]

I am a track coach and like to use Excel to organize meet results. I am having huge issues with Excel 2007. When I format the cells that are supposed to contain times for a race it always converts the time to a time of Day followed with AM or PM.

I select format cells with the block of cells I want to format selected. I choose custom and then select mm:ss.0 as I want to record 1600, 800 meter times in that particular format. I enter the first time 2:49.5 and it then displays 02:49.5...I can totally live with a leading 0...but when I click the cell that contains the time I see 12:02:49 AM in the formula bar.

Is there something I am missing or doing wrong? I have tried a number of other options with a similar lack of success.

HELP PLEASE!

 

[shg]

If you want to avoid the leading 0, change the format to m:ss.0

Excel doesn't distinguish between time intervals and times (ditto for dates, which are the same thing). What you see in the formula bar is what is set in Control Panel for your time format; it doesn't matter.

 

[Ganjin]

hmm, you could try a custom format of:
0":"00.0
then when you type:
249.5
you'll get:
2:49.5

 

[shg]

That would make it hard to use those numbers in downstream calculations.

 

[asayc423]

That is the rub I am using the custom setting but when I select mm:ss.0 it is not doing it as a stopwatch time...it is formatting it to a time of day.

It is hard for me to believe that excel does not provide a stopwatch time format, any of the custom formats I select turn into a specific time of day.

 

[shg]

It displays as you wish in the cells, does it not?