Formula help for marking a cell as blank
Hello, I'd appreciate if someone can check my work on this health formula. It's successful when tested separately and combined, though the only one proving to cause an issue is when I add in the final line (which works separately) . Thanks in advance!
Logic:
Add a red circle to this cell if due date is in the past and the status column of this row is "not started", "in progress" or "waiting for review". Add a yellow circle to this cell if [Due Date] is 2 or less days in the future and the Status column of this row is "not started", "in progress" or "waiting for review". Add a green circle to this cell if [Due Date] is in the future with more than 2 [Days Remaining] and the [Status] column of this row is "in progress" or "waiting for review". Add a blue circle to this cell if the % Complete is 100%. Add a empty to this cell if [Status] is "Not Started" and the [Due Date] cell is blank.
=IF(AND([Due Date]@row < TODAY(), OR(Status@row = "not started", Status@row = "in progress", Status@row = "waiting for review")), "Red", IF(AND([Due Date]@row - TODAY() <= 2, OR(Status@row = "not started", Status@row = "in progress", Status@row = "waiting for review")), "Yellow", IF(AND([Due Date]@row > TODAY(), [Days Remaining]@row > 2, OR(Status@row = "in progress", Status@row = "waiting for review")), "Green", IF([% Complete]@row = 1, "Blue", IF(AND([Status]@row = "Not Started", ISBLANK([Due Date]@row)), "empty", [Status]@row)))))
Best Answer
-
Hi Madison, you say the formula works separately, but I suspect it returns an error on rows where the due date is blank. If so, then you're looking in the wrong place for the the fix. The issue is not the final line. It's that the rest of the formula fails with a blank date... SS doesn't know how to evaluate [Due Date]@row - TODAY() <= 2 if the date is blank. So... you are trying to capture that situation with your final line. By then, though, SS already has an error.
The way to solve this is to move the final line to be the first IF() statement. Then, if it's true then you'll get "blank" and SS won't try to evaluate the rest of the formula. Like this...
=IF(AND([Status]@row = "Not Started", ISBLANK([Due Date]@row)), "empty",IF(AND([Due Date]@row < TODAY(), OR(Status@row = "not started", Status@row = "in progress", Status@row = "waiting for review")), "Red", IF(AND([Due Date]@row - TODAY() <= 2, OR(Status@row = "not started", Status@row = "in progress", Status@row = "waiting for review")), "Yellow", IF(AND([Due Date]@row > TODAY(), [Days Remaining]@row > 2, OR(Status@row = "in progress", Status@row = "waiting for review")), "Green", IF([% Complete]@row = 1, "Blue", [Status]@row)))))
I hope I've hit the crux of your problem. Let me know how it goes.
Be well
If my response was helpful or answered your question please be sure to upvote it, mark it asawesome, or mark it as the accepted answer!
Answers
-
Hi Madison, you say the formula works separately, but I suspect it returns an error on rows where the due date is blank. If so, then you're looking in the wrong place for the the fix. The issue is not the final line. It's that the rest of the formula fails with a blank date... SS doesn't know how to evaluate [Due Date]@row - TODAY() <= 2 if the date is blank. So... you are trying to capture that situation with your final line. By then, though, SS already has an error.
The way to solve this is to move the final line to be the first IF() statement. Then, if it's true then you'll get "blank" and SS won't try to evaluate the rest of the formula. Like this...
=IF(AND([Status]@row = "Not Started", ISBLANK([Due Date]@row)), "empty",IF(AND([Due Date]@row < TODAY(), OR(Status@row = "not started", Status@row = "in progress", Status@row = "waiting for review")), "Red", IF(AND([Due Date]@row - TODAY() <= 2, OR(Status@row = "not started", Status@row = "in progress", Status@row = "waiting for review")), "Yellow", IF(AND([Due Date]@row > TODAY(), [Days Remaining]@row > 2, OR(Status@row = "in progress", Status@row = "waiting for review")), "Green", IF([% Complete]@row = 1, "Blue", [Status]@row)))))
I hope I've hit the crux of your problem. Let me know how it goes.
Be well
If my response was helpful or answered your question please be sure to upvote it, mark it asawesome, or mark it as the accepted answer!
-
Resolved! Thanks for your support, Scott. Much appreciated. 🤩
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 430 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!