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)

  • Paul Newcome
    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
    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?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!