Avoid user having to enter 00 hours when using [mm]:ss format
 

I am formatting a game sheet for sports events. Time should be entered as
mm:ss which I have "custom" fomatted as [mm]:ss. However I cannot find a way
to force Excel to read an entry such as 07:28 as 7 minutes and 28 seconds. It
always assumes the user is entering hours and minutes.
Thanks for any help
SBB

60 seconds = 1 minute
60 minutes = 1 hour

They both the same "numeric" value.

Since you're locking the minutes to prevent the roll-over to hours,
How about *USING* hours and minutes, and lock the hours to prevent the
roll-over to days?
[hh]:mm

You'll get *exactly* the same display, and your data input will be much
easier then having to enter 0 hours + minutes + seconds.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Lady Luck" <Lady wrote in message
...
I am formatting a game sheet for sports events. Time should be entered as
mm:ss which I have "custom" fomatted as [mm]:ss. However I cannot find a
way
to force Excel to read an entry such as 07:28 as 7 minutes and 28 seconds.
It
always assumes the user is entering hours and minutes.
Thanks for any help
SBB

Thank you Ragdyer. This is not a bad idea. However doing stats such as
counting goals per period doesn´t work anymore with

COUNTIF(Gamepage!O9:P31;"20:00"&"<=40:00")

Even adding an extra set of :00 is not helping for some reason.

cheers

"Ragdyer" wrote:

60 seconds = 1 minute
60 minutes = 1 hour

They both the same "numeric" value.

Since you're locking the minutes to prevent the roll-over to hours,
How about *USING* hours and minutes, and lock the hours to prevent the
roll-over to days?
[hh]:mm

You'll get *exactly* the same display, and your data input will be much
easier then having to enter 0 hours + minutes + seconds.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Lady Luck" <Lady wrote in message
...
I am formatting a game sheet for sports events. Time should be entered as
mm:ss which I have "custom" fomatted as [mm]:ss. However I cannot find a
way
to force Excel to read an entry such as 07:28 as 7 minutes and 28 seconds.
It
always assumes the user is entering hours and minutes.
Thanks for any help
SBB

SBB,

Why not let it go in as hours:minutes, even though it means minutes:seconds?
The values will be 60 times what they should be, but they'll look like they
should. If you'll be doing any math on them, divide by 60 to correct for
this, e.g.: =SUM(B2:B20/60), entered as an array formula (press
Ctrl-Shift-Enter instead of Enter). This will work only when your
disallowing hours to roll to days, as you were with the original [mm]:ss.
Just change it to [hh]:mm.

I think this is what RagDyer was saying too.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Lady Luck" <Lady wrote in message
...
I am formatting a game sheet for sports events. Time should be entered as
mm:ss which I have "custom" fomatted as [mm]:ss. However I cannot find a
way
to force Excel to read an entry such as 07:28 as 7 minutes and 28 seconds.
It
always assumes the user is entering hours and minutes.
Thanks for any help
SBB

Hi
see your other post

--
Regards
Frank Kabel
Frankfurt, Germany
"Lady Luck" <Lady schrieb im Newsbeitrag
...
I am formatting a game sheet for sports events. Time should be entered as
mm:ss which I have "custom" fomatted as [mm]:ss. However I cannot find a
way
to force Excel to read an entry such as 07:28 as 7 minutes and 28 seconds.
It
always assumes the user is entering hours and minutes.
Thanks for any help
SBB