# Health status in a project plan

Options
✭✭
edited 10/16/22

Hi,

I am wondering if anybody can help me with setting up a formula that flags Red, Yellow, Green, Grey based on Status and Due Date.

If the status is complete, grey.

If the status is not complete and the due date is more than 5 working dates days in the past , then yellow.

If the status is not complete and the due date is than 10 working days in the past more, then red.

If the status is blocked or on hold, flag it as red.

For reference, status column includes: in progress, not started, complete, on hold, blocked.

Otherwise, it displays Green.

Would very much appreciate a quick response!

Many thanks

Yaya

• Overachievers Alumni
Options

Sorry, I misspelled Gray and then had a parentheses in the wrong spot. This should do it.

=IF(Status@row = "Complete", "Gray", IF(OR(Status@row = "Blocked", Status@row = "On Hold"), "Red", IF([Due Date]@row <= TODAY(-10), "Red", IF([Due Date]@row <= TODAY(-5), "Yellow", "Green"))))

• Overachievers Alumni
Options

Something like this?

=IF(Status@row = "Complete", "grey", IF(OR(Status@row = "Blocked", Status@row = "On Hold"), "Red", IF([Due Date]@row <= TODAY(-10)), "Red", IF([Due Date]@row <= TODAY(-5), "Yellow", "Green"))))

• ✭✭
Options

Thanks a lot David. Not sure why but returns as as UNPARSEABLE.

Also within this health status, Grey doesn't seem to display as a ball, but just text.

Also, when referenced in SS, the 5, 10 are working days or calendar days?

• Overachievers Alumni
Options

Sorry, I misspelled Gray and then had a parentheses in the wrong spot. This should do it.

=IF(Status@row = "Complete", "Gray", IF(OR(Status@row = "Blocked", Status@row = "On Hold"), "Red", IF([Due Date]@row <= TODAY(-10), "Red", IF([Due Date]@row <= TODAY(-5), "Yellow", "Green"))))

• ✭✭
Options

Thank you so much David. You made my dayyyyy!

• ✭✭
Options

Sorry David, two more questions:

1. On the top - there are general information on the top rows, where I don't want a rag status, how to get rid of it?
2. For project activity - where there are no dates yet (start nor due date), how do you normally mark this? with the current formula, it displays red?

Thanks

• Overachievers Alumni
Options

1 - You could convert from a column formula to a cell formula and copy the formula to all of the cells you want the status to appear on. Or you could make a note on the top column that the status is not indicative of anything. Either way should work.

2- You can either fill them in with "filler" dates, or we could write one more nested IF at the beginning of the formula that checks for them. So, if they don't have dates and you want the status to show green you would add:

IF(ISDATE([Due Date]@row) = 0, "Green"

This formula reads as "If the Due Date column does not contain a date, then mark the health as Green".

• ✭✭
Options

Thanks David!

Do you mind incorporated into the overall formula?

Also for tasks overdue less than 5 days, the Yellow sign doesn't seem to work, just a red? Not sure why.

• Overachievers Alumni
Options

The Yellow status works on the test sheet I've created for myself. I would need some more specifics on what you're seeing.

=IF(ISDATE([Due Date]@row) = 0, "Green", IF(Status@row = "Complete", "Gray", IF(OR(Status@row = "Blocked", Status@row = "On Hold"), "Red", IF([Due Date]@row <= TODAY(-10), "Red", IF([Due Date]@row <= TODAY(-5), "Yellow", "Green")))))

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!