Project Health Formula Help!

Danielle Bell

I am trying to add a column to my project plan to track the health of the lines in my plan based on the details in the % complete and due date columns. I would like to use the red, yellow and green symbols in the formula. Are there any formula's that you can recommend for tracking project health? I think the formula I am trying to build is too complicated as it is not capturing all of the components. I can build the formula to capture pieces of the logic for either the red, yellow or green, but when I attempt to combine them all together I get an error message. Below is what I would like to capture, any insight is greatly appreciated.

Need help with the line health formula.

o   Green

·        If 100%

·        If due date is more than a week in the future

o   Yellow

·        If due date is less than a week out and % complete is 99% - 50%

o   Red

·        If due date is less than a week out and % complete is 49% - 0%

·        If due date is in the past and % complete is less than 100%

Mike Wilday

Did that work for you?

Mike Wilday

Try this one. I am assuming that your columns are spelled as mine.

=IF(OR(AND([Due Date]@row <= Today(7), [% Complete]@row < .5),AND([Due Date]@row < Today(), [% Complete]@row < 1)), "Red", IF(OR([% Complete]@row =1, [Due Date]@row >= Today(7)), "Green", IF(AND([Due Date]@row <= Today(7), [% Complete]@row >= .5), "Yellow")))

Mike Wilday

Did that work for you?

Danielle Bell

Thanks so much!!! :)

Mike Wilday

You're welcome! :D Glad I got something working for you!

RobNY2

Mike

In my case, would be 40 days prior to the "Due Date" if the task is <100% is "Green", if the task >= 99% is "Yellow" if the task is <=50% is "Red"

I tried to use your formula above but it didn't work for me. 😪

If you could help me, I will appreciate very much!

Thank you

Rob

Mike Wilday

Hi Rob,

=IF(OR(AND([Due Date]@row >= Today(40), [% Complete]@row < 1),[% Complete]@row = 1),"Green", IF([% Complete]@row >= .5, "Yellow", "Red"))

• The color will be green if any task has 40 days or more and is less that 100%, OR if the task is marked at 100%.
• The color will be yellow if any a task has less than 40 days and is greater than or equal to 50%
• Anything less than 50% will be red.

RobNY2

You are the best Mike

Thank you very much!

Rob😀

RobNY2

Mike

I was thinking:

if the order is complete 40 days or more from the due date (31-OCT-22) is green

If the order is >= 0.51 and <=.99 "Yellow" from 40 days to the due date

If the order is <= 0.5) "Red" from 40 days to the due date

Right now your formula is giving me all green for any % complete from 40 days to the due date

I appreciate your help!

Rob

Mike Wilday

=IF(AND([Due Date]@row >= Today(40), [% Complete]@row = 1),"Green", IF([% Complete]@row >= .5, "Yellow", "Red"))

Mike Wilday

That should do the trick @RobNY2

RobNY2

Mike

I had to change what this formula should do because using the project due date was not working. I created two columns "Start Date & End Date" and the formula should look at the "End Date" column.

The idea is the Health should count 7 days prior to the "End Date"

If the Equip Order Status % Complete is

70% complete 7 days

Mike Wilday

Question. Should the project be green regardless of %complete if the end date is greater than 7 days from today? Also, what is the requirement for yellow?

This formula calculates any date further out than 7 days as green.

IF(TODAY(7) < [End Date3]@row, "Green", IF(AND([End Date3]@row >= TODAY(7), [Equip Order Status % Complete]@row >= 0.7), "Green", IF([Equip Order Status % Complete]@row >= 0.5, "Yellow", "Red")))

RobNY2

Mike

Here the link with more explanation

Task Health — Smartsheet Community

Rob