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

  • Scott Orsey
    Scott Orsey ✭✭✭✭✭
    edited 03/18/24 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

Answers

  • Scott Orsey
    Scott Orsey ✭✭✭✭✭
    edited 03/18/24 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

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!