Adjusting formula to deal with blank fields

IF({Due Date} < {Date Today},“Yes”,“No”)

This is a simple formula that I use to identify a list of items that are overdue. The problem is that instead of returning error when due date is blank, it returns Yes.

What change should I make in my formula? Thanks for helping out a Novice!

Hi Hilal,

It depends on what you want to get when the due date is blank. I am confused because in your formula, if the due date is passed, you get “No” and if it is not passed you get “Yes”. Based on your explanation, I would have expected the reverse.

Anyway if you want to display “No”, then use:

IF(AND({Due Date}<>"",{Due Date}<{Date Today}),"Yes","No")

If you want to display “Yes”, then use:

IF(OR({Due Date}="",{Due Date}<{Date Today}),"Yes","No")

If you want the column to remain blank:

IF({Due Date}="","",IF({Due Date}<{Date Today},"Yes","No"))

If you’re looking for a way to make your formulas a lot simpler to write, take a look at the Advanced Formula Booster.


Hi Gilles

Thank you for the suggestions. Yes, it helps. The third formula is not working though.

there was a misplaced parenthesis, it works now…

IF({Due Date}=“”,“”,IF({Due Date}<{Date Today},“Yes”,“No”))

Indeed. I corrected it for future readers.
Glad it helped.