lundi 1 février 2021

Google Sheets - IF Statement - Null date (1/1/2500) workaround

I am working on a large nested IF statement that checks several validation points for each row of my sheet. There are several date validations, including chronological order and certain fields not being future dates. However, our system requires that if we must null any dates for processing, that date becomes 1/1/2500, and no matter what I do I cannot seem to get the formula to ignore this date when accounting for future dates or chronology.

IF(K1>12/31/2099,"",IF(AND(K1>TODAY(),K1<>"",K1<>"1/0/1900"),"Date A cannot be future date",""))

IF(U1>12/31/2099,"",IF(AND(U1>AA1,AA1<>""),"Date A, Date B should be in chronological order",""))

I tried the approach above which seems to ignore every future date.
I also tried going with <>1/1/2500 but it just doesn't catch in the formula.
Any help would be appreciated.

Aucun commentaire:

Enregistrer un commentaire