Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I keep a cells format, general, from changing after enter. | Excel Discussion (Misc queries) |