Data validation: Valid date + Age of majority

[gerotutu]

Hi experts!

I have been trying to make a data validation work. It's simple. I need to be sure, firstly that the data in column I is a valid date. Secondly, if the column D is not "NIT" it should be greater than 1/1/1900. For any other value in column D I need to be sure that the date is more than 18 years old.

I built this formula but despite it works in a normal cell, it doesn't work in the "custom data validation" option

=IF(OR(NOT(ISBLANK(I3));DATE(YEAR(I3);MONTH(I3);DAY(I3)));IF(D3="NIT";I3>=DATE(1;1;1900);TODAY()-I3>=6575);FALSE)

Any clue why? Many thanks!
Gerónimo

 

[liveinhope]

So if value in D = "NIT" then date must be 18 years old
if value in D is other than NIT any date after 1/1/1900 (eg today) will do

If that is what you want Try this =IF(D3="NIT",AND(ISNUMBER(I3),I3 <=edate(today(),-216)),AND(ISNUMBER(I3),I3 > 1 ))

The EDATE part of formula checks that date is 216 months (12 x 18 = 216) months earlier than today

 

[gerotutu]

It was the other way around so I just changed the "=" with "<>" and seems to work perfectly!

Thank you very much.

 

[gerotutu]

Maybe you could help me again for another data validation. I need to check if the field that is completed in column W is a number of at least 7 characters and in the case the field in column V is "TARJETA_CREDITO" I do the check that is below inside the XOR (some basic rules to validate different types of credit cards).

I tried it out in a normal cell and it works but it does not work properly when I use it as data validation.

=IF(V3="TARJETA_CREDITO";
AND(ISNUMBER(VALUE(W3));XOR(AND(LENGTH(W3)=14;LEFT(W3;2)="36");AND(LENGTH(W3)=16;OR(LEFT(W3;1)="5";LEFT(W3;1)="4"));AND(LENGTH(W3)=15;LEFT(W3;2)="37")));
AND(LENGTH(W3)>=7;ISNUMBER(VALUE(W3)))
)

Why the data validation only works when I select "TARJETA_CREDITO? The other path of the IF never works.
Thanks, again!