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(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.

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"))))

Omid

Hi Paul,

I have columns "% Complete", "Start" and "Finish". I have adjusted your formula to match my columns and I can't seem to get it working. Can you see if I'm doing anything wrong?

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

Genevieve P.

Hi @Omid

It looks like there may be some extra parentheses in here. I've bolded the areas where you should only have one closing parentheses:

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

Then make sure you only have 3 at the very end of the entire formula.

Cheers!

Genevieve

Paul Newcome

@Genevieve P. Good catch! Looks like I fat fingered an extra one in mine.

@Kelly Pratt Please see Genevieve's response.