Schedule Health Based off Duration and % Complete
Hi  I'm trying to write a task health formula with the following criteria and am hoping someone can help. Here's what I'd like it to calculate:
 GREEN: If today is less than 50% of the task duration and % complete is more than 50%
 YELLOW: If today is more than 50% of the task duration and % complete is less than 50%
 RED: If today is more than 75% of the task duration and % complete is less than 75%
Thanks in advance!
Answers

Additional requirements:
Green if start date is after todays date
Red if end date is before todays date (if not 100% Complete)

Instead of worrying about set percentages (50% compared to 50% and 75% compared to 75%) it is actually easier to do a direct comparison of planned vs actual. So if it is supposed to be 27% based on duration, we change the color based on actual % Complete.
The only other thing to think about with this method is your Yellow tolerance. Basically, how much variation will you allow for yellow before we switch to red. If a 5% variation then it would look something like this...
=IF([% Complete]@row >= NETWORKDAYS([Start Date]@row, TODAY()) / Duration@row, "Green", IF(([% Complete]@row >= NETWORKDAYS([Start Date]@row, TODAY()) / Duration@row)  0.05, "Yellow", "Red"))
If the row is supposed to be 50% then you will get the following outputs:
>=50% = Green
45%  50% = Yellow
<45% = Red
To adjust the tolerable variance on the Yellow, change 0.05 to whatever percentage variance you prefer. Smartsheet formulas read percentages as a decimal, so 0.05 = 5%, 0.25 = 25%, so on and so forth.

Hey @Paul Newcome ,
I just tried this formula, however I ran into an error with milestones (can't divide by 0) so I modified it to an IFERROR and then put Green as the IFERROR result. I also tried modifying it to 20 percent.
Here's the new query.
=IFERROR(IF([% Complete]@row >= NETWORKDAYS([Start Date]@row, TODAY()) / Duration@row, "Green", IF(([% Complete]@row >= NETWORKDAYS([Start Date]@row, TODAY()) / Duration@row)  0.2, "Yellow", "Red")), "Green")
Any idea what I'm doing wrong here? Maybe my understanding of the query is off based?
Help Article Resources
Categories
Check out the Formula Handbook template!