Health status in a project plan

Yaya

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

David Tutwiler

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

David Tutwiler

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

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

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

Thank you so much David. You made my dayyyyy!

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

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

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

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