Data Validation Query: - Time H:MM Format

  • Thread starter Thread starter Bikini Widow
  • Start date Start date
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
 
Instead of the CUSTOM validation tey the TIME validation and enter the
range you want to allow

Frank
 
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
 
In the TIME validation you can enter a range (so your starting value is
0:01 and your highest value is 09:59)

Frank
 
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
 
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.
 
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
 
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
 
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
 
In my version (2003) I can enter any values as long as there decimal values
are less than 09:59 (0.415972222222222)
 
Hi Guys,

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

Thanks again,
B
 
Back
Top