Date validation

[jpreman]

Thanks for reading this post.

In column A I would like to enter dates. For instance the date entered in A5
should be verified for the following.

Question :-

1 If A4 is blank, no entry should be permitted in A5
2 If A4 is not blank, then A5 could be equal to or greater than A4


Alternate :-

1 If A4 is blank, no entry should be permitted in A5
2 If A4 is not blank, then A5 should be equal to TODAY( )

What should be the CUSTOM/FORMULA for both the above validations?

Thanks in advance

Regards

Preman

 

[muddan madhu]

data validation for the cell A5

=IF(A4="",A5<>"",IF(A4<>"",A5>4,""))

 

[T. Valko]

For the question:

Uncheck Ignore blank

=AND(COUNT(A4),COUNT(A5),A5>=A4)

For the alternate:

=AND(COUNT(A4),A5=TODAY())

 

[jpreman]

Great !

Thanks a lot Valko. That's exactly what I was looking for.

I would be glad if you kindly explain how COUNT function works in your
formula.

Thanks & regards

Preman

 

[T. Valko]

Since you're validating dates the COUNT function makes sure a date is
entered in either cell. In Excel dates are really numbers formatted to look
like a date. So, if a date (number) is not entered in A4 then the formulas
evaluate to FALSE and the validation will not permit an entry into A5.

 

[jpreman]

Got it.

Once again many thanks to you.

Kind regards

Preman

 

[jpreman]

Thanks for responding to my post Madhu.

The formula did not satisfy condition 1. Perhaps you can take have a look
and correct it.

Regards

Preman

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Got it.

Once again many thanks to you.

Kind regards

Preman