Avoid user having to enter hh into mm:ss formatted cell

[Ladyluck]

Hi all
I´m working on a simple sports-score sheet. The user will enter thing such as goals and penalties during a game and Excel will pull together some stats for a game summary.
I´ve formatted the time column as [mm]:ss with [] preventing roll-over to hours. However when the user enters 07:28 Excel will register it as 7 hours and 28 minutes, and only reads the time correctly if the user writes 00:07:28, which is cumbersome and will cause endless errors and eventually mutiny.

At another user group two different soultions were suggeted: One which involves using [hh]:mm instead which is not a bad idea but after adopting it my conditional count statements don´t work e.g.

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

works fine for [mm]:ss but not for [hh]:mm and changin the condition to ;">20:00:00"&"<=40:00:00") doesn´t help either.
The second solution involves using VBA which I´m sure is great but I do not have the time or energy to learn enough VBA to write edit or maintain such scripts (see

http://www.cpearson.com/excel/datetime.htm#TOC).

So I´m hoping someone here has a bright idea.
thanx beforehand
LL

 

[xlbo]

Something that might give you an idea.....
If you enter 10:00 into a cell you format as hh:mm:ss, it will show as 10:00:00
Divide this cell by 60 and you will get 00:10:00

So...maybe you can have an "entry" area where the user imputs the time as they perceive it to be (ie in minutes) - this would actually be input as 10 hours BUT you actually calculate from a DIFFERENT cell which divides the originally entered value by 60......

Other than that, I think you would need the VBA solution


Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question