IF AND Formula with IS NOT NULL in Excel

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.)

2 Answers

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.

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