How do I enter minutes and seconds (lap times)?

[SeanP]

I wish to enter lap times from a race. These are measured in minutes and
seconds. No matter how I format the cell, I end up with dates. I want to be
able to manipulate this data (e.g. graph, sum, avg., etc.)

[Bernard Liengme]

You must enter the hours: so 4 min 25 sec is entered as 0:4:25
You can format cells as mm:ss, m:ss, or mm:ss.00

best wishes

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"SeanP" <[email protected]> wrote in message
news:[email protected]...
>I wish to enter lap times from a race. These are measured in minutes and
> seconds. No matter how I format the cell, I end up with dates. I want to
> be
> able to manipulate this data (e.g. graph, sum, avg., etc.)

[brada1703]

Bernard,

That doesn't work. When I enter "0:4:25", excel shows in the function box "12:04:25 AM." I've tried to change the format to custom HR:MM:SS, but it still won't work. I want to type in the information to then use it... not just to display correctly.

You must enter the hours: so 4 min 25 sec is entered as 0:4:25
You can format cells as mm:ss, m:ss, or mm:ss.00

best wishes

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"SeanP" <[email protected]> wrote in message
news:[email protected]...
>I wish to enter lap times from a race. These are measured in minutes and
> seconds. No matter how I format the cell, I end up with dates. I want to
> be
> able to manipulate this data (e.g. graph, sum, avg., etc.)

[joeu2004]

That doesn't work. When I enter "0:4:25", excel shows in the function box "12:04:25 AM." I've tried to change the format to custom HR:MM:SS, but it still won't work. I want to type in the information to then use it... not just to display correctly.

First, you are "responding" to a 9-year-old thread. It would be better to start a new thread.

Second, alternatively, you can enter the data in the form m:s.0. The ".0" causes Excel to interpret x:y as x minutes and y seconds instead of x hours and y minutes.

Finally, we cannot control the appearance in the Formula Bar. It has its own formatting rules, which are mostly independent of the cell format.

Note: This is problematic if we enter non-zero fractions of a second, e.g. 12:34.567. Although the initial data entry is correct, we cannot easily edit it later because the fractional seconds do not appear in the Formula Bar (or in the cell in edit mode, if that option is set).

If you want the cell format to be Custom m:ss, you must set it manually before or after entering the data. The form of the data entry (0:m:s or m:s.0) does not matter.

Alternatively, you could set up a Worksheet_Change event macro that changes the cell format appropriately. However, it is tricky to limit the effect of the macro to only the cells you intend to change. Not worth the trouble, IMHO.