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

[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

[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.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Lady Luck" <Lady [email protected]> wrote in message
news:[email protected]...
> 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

[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" 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 [email protected]> wrote in message
> news:[email protected]...
> > 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
>
>

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

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

"Lady Luck" <Lady [email protected]> wrote in message
news:[email protected]...
>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

[Frank Kabel]

Hi
see your other post

--
Regards
Frank Kabel
Frankfurt, Germany
"Lady Luck" <Lady [email protected]> schrieb im Newsbeitrag
news:[email protected]...
>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