ISBLANK Formula Error
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")), "")
Best Answer
-
You may need to just delete the blank rows. The formula shouldn't drop-down until there's a row created.
Answers
-
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!
-
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...
-
You may need to just delete the blank rows. The formula shouldn't drop-down until there's a row created.
-
I hadn't made it a column formula yet. Thanks for your help!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!