Task Health Calculation using duration and % complete

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([email protected] / 2)), [% Complete]@row >= 0.5), "Green", IF(AND([Start Date]@row > TODAY([email protected] * 0.5), [% Complete]@row < 0.5), "Yellow", IF(AND([Start Date]@row > TODAY([email protected] * 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.

Paul Newcome

I usually use the expected % complete compared to the actual to allow for a little more flexibility/accuracy.

Expected % Complete:

=(TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row))

Using this you can do an actual comparison. Generally speaking I use the following logic:

On Track/Ahead of Schedule = "Green"

Behind Schedule by less than 5% = "Yellow"

Behind Schedule by more than 5% = "Red"

Complete = "Blue"

=IF([% Complete]@row = 1, "Blue", IF((TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row))>= [% Complete]@row, "Green", IF((TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row))>= [% Complete]@row - 0.05, "Yellow", "Red"))))