Ignoring Blank Dates

ChristinaB

I have a sheet that is checking for past due dates.

This formula works to add a check mark when the task is completed on time:

=IF([Task Closed Date]@row <= [Due Date]@row, 1, 0)

But it counts the blank dates in "Task Closed Date". I have tried mimicking other formulas that utilize ISBLANK, but none of them have worked.

ECarlson_PCG

=IF([Task Closed Date]@row = "", 0, IF([Task Closed Date]@row <= [Due Date]@row, 1, 0))

^this is saying, If Task Closed Date equals Blank then be 0 (or do not check the box) in formula

Let me know if this doesn't work.

Laurie Olson

Try this:

=IF(AND([Task Closed Date]@row <= [Due Date]@row, [Task Closed Date]@row <> ""), 1, 0)

I find it is more useful to use the AND clause, as then you can add more criteria as you test. You may consider only looking at the children tasks vs flagging parent tasks as well. You can add that, as well, to the AND clause.

ECarlson_PCG

=IF([Task Closed Date]@row = "", 0, IF([Task Closed Date]@row <= [Due Date]@row, 1, 0))

^this is saying, If Task Closed Date equals Blank then be 0 (or do not check the box) in formula

Let me know if this doesn't work.

Nathaniel Adamian

I set up a test case in a sheet of my own and found that

=IF([Task Closed Date]@row < = [Due Date]@row, IF(ISBLANK([Task Closed Date]@row), 0, 1), 0) worked.
If that doesn't work could you clarify your column types so we can isolate the difference?

ChristinaB

Thank you all! The first solution worked perfectly and I don't know why I didn't try that to begin with.