Data Validation - Again!

[mlv]

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

 

[Martin Kral]

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:

 

[Martin Kral]

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

 

[Dave Peterson]

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)))

 

[mlv]

Martin Kral wrote:
<

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 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.

 

[mlv]

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 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.