Schedule Health Based off Duration and % Complete

RFedders

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!

RFedders

Additional requirements:

-Green if start date is after todays date

-Red if end date is before todays date (if not 100% Complete)

Paul Newcome

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.

dhall

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?