Data Validation

Data Validation - Again!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mlv - 22 Apr 2008 09:51 GMT

Once again I'm struggling with the 'Custom' option in Data Validation.

I have a cell formatted as 'Date' (assume A1).

I want the user to be able to enter any valid date greater than (say)
01/01/2000.

Alternatively, I also want the user to be able to enter a single character
(the letter 'X' for example) instead of a date, which will be used by
another formula to perform an alternative date function.

I have been trying numerous variations of the following custom formulas:

1. =AND(UPPER(A1)="X",A1>=DATE(2000,1,1))

2. =OR(UPPER(A1)="X",A1>=DATE(2000,1,1))

I can get the letter 'X' (upper or lower case) to validate correctly, but
not the date.

Maybe I'm not understanding how Data Validation works.

I assume that the data entered into the cell has to match what the
validation formula is looking for (which would suggest that formula 2. is
the one to use - either the data is an 'X', OR it is a date greater than
01/01/2000, otherwise the data is incorrect).

Please can someone provide a working formula... and an explanation?

TIA

Signature

Mike
-Please remove 'safetycatch' from email address before firing off your
reply-

Reply to this Message

Martin Kral - 22 Apr 2008 11:12 GMT

Hi Mike,

I think (at least it works for me under most conditions) that you have to
check for the entry being a number as well.

If you don't, the formula will perform a textual comparison and any string,
e.g. "A" will be greater then the number you get by using DATE().

Try:

> Once again I'm struggling with the 'Custom' option in Data Validation.
>
[quoted text clipped - 26 lines]
>
> TIA

Reply to this Message

Martin Kral - 22 Apr 2008 11:17 GMT

Hi Mike,

try:

=OR(UPPER(A1)="X",AND(ISNUMBER(A1),A1>=DATE(2000,1,1)))

I believe you have to check for the entry being a number before comparing to
a date serial. Otherwise any string, e.g. "A" will fulfill the condition of
being 'greater then'. ="A">=DATE(2000,1,1) returns TRUE.

Martin

> Once again I'm struggling with the 'Custom' option in Data Validation.
>
[quoted text clipped - 26 lines]
>
> TIA

Reply to this Message

Dave Peterson - 22 Apr 2008 12:37 GMT

Just to add to Martin's response:

The =upper() function isn't required. If you're doing a simple text comparison,
excel will return true for:

=UPPER(A1)="x"

If you really only want uppercase X's, you could use =exact():

=OR(EXACT(A1,"X"),AND(ISNUMBER(A1),A1>=DATE(2000,1,1)))

> Hi Mike,
>
[quoted text clipped - 42 lines]
> > -Please remove 'safetycatch' from email address before firing off your
> > reply-

Signature

Dave Peterson

Reply to this Message

mlv - 22 Apr 2008 17:46 GMT

> Just to add to Martin's response:
>
[quoted text clipped - 6 lines]
>
> =OR(EXACT(A1,"X"),AND(ISNUMBER(A1),A1>=DATE(2000,1,1)))

Hi Dave

I was falling back on my AutoLisp programming knowledge when I wrote the
formula and I wanted to make sure that both an uppercase 'X' and a lowercase
'x' would be accepted.

In AutoLisp the comparison is case sensitive, so it is necessary to convert
the text to a known case and compare like for like:

i.e.

(= "x" "X") would return nil (False), whilst

(= (strcase "x") "X") would return T (True)

It didn't occur to me that Excel might not need the text case conversion.

Thanks for the information.

Signature

Mike
-Please remove 'safetycatch' from email address before firing off your
reply-

Reply to this Message

mlv - 22 Apr 2008 17:23 GMT

> try:
>
[quoted text clipped - 3 lines]
> to a date serial. Otherwise any string, e.g. "A" will fulfil the condition
> of being 'greater than'. ="A">=DATE(2000,1,1) returns TRUE.

Hi Martin

Thanks for your help, that seems to work just fine.

So many pitfalls, so much to learn!

I could do with a good source of Excel custom formulae.

Signature

Mike
-Please remove 'safetycatch' from email address before firing off your
reply-

Reply to this Message

Rate this thread: