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

L

Lady Luck

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
 
R

Ragdyer

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

Lady_luck

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 said:
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 said:
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
Click to expand...
Click to expand...
 
E

Earl Kiosterud

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