Project Health Formula Help!
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%
Best Answer

Did that work for you?
Answers

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

Did that work for you?

Thanks so much!!! :)

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

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

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.

You are the best Mike
Thank you very much!
Rob😀

Mike
I was thinking:
if the order is complete 40 days or more from the due date (31OCT22) 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

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

That should do the trick @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

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

Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.4K Get Help
 364 Global Discussions
 200 Industry Talk
 430 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!