ISBLANK Formula Error

Sarah Brelage

I've been working on variations of this formula for an hour and can't get it right. I've been close but it hasn't returned what I was expecting.

I have 3 columns - Actual Completion Date, Status and Due Date. If the Due Date < Today and Status is not Closed then I want it to return 'Past Due'. The problem is that some users are entering the Actual Completion Date and not moving the Status to 'Closed'. If that is the case, then I don't want it to return anything.

My other issue is that when I had blank rows, the cell was returning an INVALID OPERATION error.

=IFERROR(IF(ISBLANK([Actual Completion Date]@row)), (IF(AND([Status]@row <> "Closed", [Due Date], < TODAY(), "Past Due")), "")

Javed Hassan

You may need to just delete the blank rows. The formula shouldn't drop-down until there's a row created.

Javed Hassan

Hello!

Can you try this formula?

=IF(ISBLANK([Actual Completion Date]@row), IF(AND(Status@row <> "Closed", [Due Date]@row < TODAY()), "Past Due", ""), "")

The error wasn't in your ISBLANK section, but with your second IF statement as well as your AND statement.

Hope this helps!

Sarah Brelage

Thanks! For the most part, it is working. The only piece that isn't working is that it is adding a Past Due in the column for blank rows that have yet to be filled in...

Javed Hassan

You may need to just delete the blank rows. The formula shouldn't drop-down until there's a row created.

Sarah Brelage

I hadn't made it a column formula yet. Thanks for your help!!