blank cell > than date??

[ajm]

Hi all, i am struggling with a formula that compares two date fields. the first date field is built by formula and the second is static (hard coded in a cell at the top of the page).

Formula: IF(AND(L2>=$S$1,OR(M2=$V$1,M2=$W$1,M2=$U$1)),"Type A","Type B") where

L2 is a Date (Date 1)
S1 is a static date (Date 2)
M2 is payment amount
U,V,W are amounts

So the formula reads " if Date 1 is greater than or equal to Date 2 AND the payment amount is the same as U,V, or W, then this is a Type A. Everything else is a Type B.

in all cases when Date 1 is blank, the result returned is Type A. How can the Blank Date be greater than the static date? How can I amend the formula to function correctly?

 

[Radoslaw Poprawski]

becouse that cell is treated as empty text string which has bigger value in excel:
in your case I would start with IFS function and I would go: ifs=(yourcell ref = "";"Type A"; here post every other case)

 

[joeu2004]

Formula: IF(AND(L2>=$S$1,OR(M2=$V$1,M2=$W$1,M2=$U$1)),"Type A","Type B") where
L2 is a Date (Date 1)
S1 is a static date (Date 2)
[....]
when Date 1 is blank, the result returned is Type A. How can the Blank Date be greater than the static date? How can I amend the formula to function correctly?

Correction: when L2 __appears__ to be blank, but its value is actually the null string or a string of characters that appears to be blank, then L2>=S1 is TRUE.

The reason: in Excel, any text is treated as greater than any number. It's an arbitrary choice.

To confirm: verify that ISBLANK(L2) returns FALSE, and ISTEXT(L2) returns TRUE.

If you do not see a string or a formula that returns a string in L2, the reason might be: someone copy-and-pasted-value into L2.

In contrast, if L2 is truly empty (no value; ISBLANK(L2) returns TRUE), L2>=S1 is FALSE because Excel treats L2 as zero in that context.

The simplest fix:

IF(AND(N(L2)>=$S$1, OR(M2=$V$1, M2=$W$1, M2=$U$1)), "Type A", "Type B")

The N() function returns zero if its argument (L2) is text. Otherwise, it returns the numeric value of L2.