Excel Data Validation doesn't error but formula shows false.

Question

D12 Entered in date format: 4/50/2000 and E12 in Date format is blank.

Did Custom to D12:

=AND(ISNUMBER(D12),LEFT(CELL("format",D12),1)="D",OR(ISBLANK(E12),D12<E12))

Data Validation does not error, but the Formula will show False.

If I do in Data Validation this works: AND(AND(ISNUMBER(D12),LEFT(CELL("format",D12),1)="D"))

How do I add tot he Data Validation for D12 also checking D12<E12 plus the Date entered into D12 is valid?

Thank you

Answer 1

I found the problem and it fixed it.

Originally I have .IgnoreBlank = True

The fix was to set:

.IgnoreBlank = False

IgnoreBlank was ignoring the Formula reference to E12

D12 & E12 I have valuables used for this but changed to show here.

'Macro code to apply the Data Validation.

Range("D12").Select 

With Selection.Validation 

    .Delete 

    .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= \_ 

    xlBetween, Formula1:="=AND(ISNUMBER(D12),LEFT(CELL(""format"",D12),1)=""D"",OR(ISBLANK(E12),D12<E12)) " 

    .IgnoreBlank = False 

'(removed rest below for simplicity)

Answer 2

Barney,

Here's the prove:

Sample file:

https://www.dropbox.com/s/6v5w93lina62mzk/d53a7d0d-b5b3-4520-8c3a-f8b6c4ffbe01.xlsx?dl=1

Maybe you applied the CF to two (or more) columns as in my sample file!? In this case the column references must be absolute:

=NOT(AND($D12<$E12,$D12>0,$E12>0))

Andreas.

Answer 3

Data Validation still does not error with D12 --> 4/50/2000.

D12 and E12 are formatted as a Date 3/14/2000.

I changed the Data Validation custom formula for D12 to:

=AND(ISNUMBER(D12),LEFT(CELL("format",D12),1)="D",NOT(AND(D12<E12,D12>0,E12>0)))

When I put in D12 --> 4/50/2000

The formula shows False, but the Data Validation does not error.

Answer 4

=NOT(AND(D12<E12,D12>0,E12>0))

is enough to cover all cases.

Andreas.