IF AND Formula with IS NOT NULL in Excel

Question

I am using Excel 2010 and currently trying to get a formula for my data using a Nested If And, but unable of the correct formula.

Here is some sample data to elaborate on my point:

(A1)   Received Date    (B1)  DueDate
(A2)  7/1/2016          (B2)  7/8/2016
(A3)  7/1/2016          (B3)  6/29/2016
(A4)  7/1/2016          (B4)  NULL

Basically, I want to create a formula that satisfies the following conditions. If Received Date < DueDate AND DueDate IS NOT NULL...then "YES", else "NO". So in this sample code above, only the first record should return "YES" and the other two should return "NO."

How do I do about a formula doing this?

I don't know how to do the second condition, the IS NOT NULL part. I put the cell numbers in parenthesis to simulate the table. (Hope that helps.)

Solution 1

Excel does not have a function to test for null. You can use IsBlank() to check for a blank cell or you can use IsNumber() to check for a numeric value. So, in your case something like

=if(and(isnumber(B2),A2<B2),"something","else")

Edit: If you want to check for the text "Null", then

=if(and(B2<>"Null",A2<B2),"something","else")

= is the "equals" comparison operator. "Not equals" is done with the <> comparison operator. Or you could do Not(B2="Null") but that's a bit too curly.

Another edit: FWIW, the first formula should still work, regardless of the cell containing text or being blank. As soon as the cell contains a date (which is a numeric value), the condition will be TRUE. So you can use that formula as well.

Solution 2

=IF(AND(B2<>"NULL",A2<B2),"YES","NO")

Comments

[smul86]

I know there isn't a direct way to check for NULL, but the column value is actually the word NULL. Is there a way to go through the column and find the columns that just have the word NULL in there?

[smul86]

Or do you know a way to check for ISERROR using IF AND function?

[teylyn]

You mean you don't know how to check if a cell contains a specific word? I added that to my post.

[smul86]

Thank you, this helped me out! Appreciate you @teylyn.