Formula for task health
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
Help Article Resources
Check out the Formula Handbook template!