Formula help for marking a cell as blank

Madison Bales

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)))))

Scott Orsey

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

Scott Orsey

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

Madison Bales

Resolved! Thanks for your support, Scott. Much appreciated. 🤩