Task Health Calculation using duration and % complete

Kelly Pratt
Kelly Pratt ✭✭✭✭✭

I am working on a formula to calculate task health with R/Y/G harvey balls based on the duration of a task and the % complete. I have found several community discussions around this but the ones I have found , I have not been able to successfully tweak to work in my sheet.

In my sheet, I have Start Date, End Date, Duration, and % Complete columns. Here is a formula I've been testing with. Some row it shows up correctly and others show Invalid Data Type.

=IF(AND([Start Date]@row < TODAY(ROUND(Duration@row / 2)), [% Complete]@row >= 0.5), "Green", IF(AND([Start Date]@row > TODAY(Duration@row * 0.5), [% Complete]@row < 0.5), "Yellow", IF(AND([Start Date]@row > TODAY(Duration@row * 0.75), [% Complete]@row < 0.75), "Red")))


Can anyone see what I am missing here or potentially a better/different formula to use?


Here is the criteria, I am trying to use:

If today is less than or equal to 50% of the task duration and % complete is more than or equal to 50%, return green.

If today is more than 50% of the task duration and less than 75% of the task duration and % complete is less than 50%, return yellow.

If today is more than or equal to 75% of the task duration and the % complete is less than or equal to 75%, return red.

image.png


Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!