Health status in a project plan

Yaya
Yaya ✭✭
edited 10/16/22 in Formulas and Functions

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

Best Answer

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Answer ✓

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

Answers

  • David Tutwiler
    David Tutwiler Overachievers Alumni

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

  • Yaya
    Yaya ✭✭

    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?

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Answer ✓

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

  • Yaya
    Yaya ✭✭

    Thank you so much David. You made my dayyyyy!

  • Yaya
    Yaya ✭✭

    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

  • David Tutwiler
    David Tutwiler Overachievers Alumni

    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".

  • Yaya
    Yaya ✭✭

    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.

  • David Tutwiler
    David Tutwiler Overachievers Alumni

    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!