Data Validation Query: - Time H:MM Format

B

Bikini Widow

Hi Guys,

First Post. I need help with the following:


I have a row of cells that is custom formatted as H:MM

1) Users enter their overtime in hours and minutes (H:MM)

What I am trying to do is have data validation that they can only put
their overtime H:MM

2) I try and validate their input by going to DATA |
VALIDATION | ALLOW | CUSTOM | and custom = H:MM

For some reason this will not work. Does anybody know
how to get this working?

Any Help would be much appreciated.

Rgds,
Bw
 
F

Frank Kabel

Instead of the CUSTOM validation tey the TIME validation and enter the
range you want to allow

Frank
 
B

Bikini Widow

Thanks guys,

Problem is the range could be anything from 0:01 to 9.59

would the easiest way be just to list 0:01 to 9.59 out in a column and
use Time?
Rgds,
Bw
 
F

Frank Kabel

In the TIME validation you can enter a range (so your starting value is
0:01 and your highest value is 09:59)

Frank
 
B

Bikini Widow

Thanks guys,

That works a treat, except for one flaw if the user enters anything
between:

0.01 comes up as 0:14
to
0.30 comes up as 6:57


anything higher than 0.30 I get the correct validation MSGBOX

The cell format is H:MM

The Cell validation is - allow time between
00:01:00
to
07:00:00

Any Ideas

Rgds,
BW
 
P

Paul

Bikini Widow > said:
Thanks guys,

That works a treat, except for one flaw if the user enters anything
between:

0.01 comes up as 0:14
to
0.30 comes up as 6:57


anything higher than 0.30 I get the correct validation MSGBOX

The cell format is H:MM

The Cell validation is - allow time between
00:01:00
to
07:00:00

Any Ideas

Rgds,
BW
Click to expand...

0.01 is interpreted by Excel as one-hundredth of a day, which is 14.4
minutes. With a format of H:MM this will be displayed as 0:14

I think you need to understand that data validation is concerned only with
the value that you can enter into a cell. It does not force you to enter
that value in any particular way. It does not control what you can and
cannot type. It does not force you to enter time in a time format (such as
0:01). So, it allows you to type in 0.01 as this can be interpreted as a
time within your specified range.
 
F

Frank Kabel

The user has to enter his time as 0:01 (he has to use the colon as
seperator). In my Excel version (2003) i'm not allowed to enter '0.01')

Frank
 
P

Peo Sjoblom

Note that it won't prevent anyone pasting any value or someone typing for
instance 0.25 for 15 or 25 minutes
which will be changed to 06:00 hours
 
F

Frank Kabel

Sorry

you're right, of course you can enter '0.01'. I'm not allowed to to
this, as i use the German regional setting (so i can enter '0,01')
Frank
 
P

Peo Sjoblom

In my version (2003) I can enter any values as long as there decimal values
are less than 09:59 (0.415972222222222)
 
B

Bikini Widow

Hi Guys,

Thanks for all your advice, will try it any see, maybe some luck
people will get overpaid!!

Thanks again,
B
 
Top