Formula for task health

rick.williams

I am looking for a formula to identify task heath, based on duration, % complete and target due date, where the health is based on duration of task, such as 5d and if there is enough time to complete the task and the %completed - so a 10d task that is more than 10d before target date would be green. That same task would remain green if it were only a week prior as long as at least 50% of the task is complete. The color would change to yellow if the task is 5days or less out and the % is less than 50% complete, or if the task is up to 5d after the target date. Anything after that is red. if the project is on hold, then the color is amber.

green = %duration up to 50% before target due date

green = 50% > before duration and 50% > task completion

yellow = up to 1 week after target due date

yellow = %duration > 50 and task completion < 50%

red = more than one week after target due date

amber = hold

Genevieve P.

Hi @rick.williams

Try something like the following:

=IF(Status@row = "Hold", "Gray", IF(AND(TODAY() > WORKDAY([End Date]@row, 5), [% Complete]@row <> 1), "Red", IF(AND([% Complete]@row < 0.5, NETWORKDAYS([Start Date]@row, TODAY()) / Duration@row > 0.5), "Yellow", "Green")))

I'll break down what it says in order.

Gray Statement

There is no Status column that returns both a Yellow and an Amber status ball, but we can add a Gray ball in when the Status is on Hold. We want this to be the very first thing so the formula doesn't output any other colour if this word is in your Status cell:

=IF(Status@row = "Hold", "Gray",

Red Statement

=IF(AND(TODAY() > WORKDAY([End Date]@row, 5), [% Complete]@row <> 1), "Red",

First we tell the formula when to turn Red. This is only if it meets two criteria:

% Complete is not 100%

• [% Complete]@row <> 1

AND If Today is 5 Working Days past the End Date

• TODAY() > WORKDAY([End Date]@row, 5)

Yellow Statement

Then we can move on to Yellow:

IF(AND([% Complete]@row < 0.5, NETWORKDAYS([Start Date]@row, TODAY()) / Duration@row > 0.5), "Yellow",

If the % Complete is less than 50%

• [% Complete]@row < 0.5

AND If the number of working days from the Start of the task until Today is more than 50% of the Duration

• NETWORKDAYS([Start Date]@row, TODAY()) / Duration@row > 0.5

Green Statement

Finally, we can simply say that if none of the above criteria are met, the row is Green. This is because the formula will only get to this point if the row doesn't match any of the above criteria.

Let me know if this works for you!

Cheers,

Genevieve