# Help with calculating health (R/Y/G/B) based on end date and % complete

Options
✭✭✭
edited 05/05/20

Hi,

I'm trying to create a formula to auto calculate the health of a task and return the correct R/Y/G/B ball based on the % complete measured against the planned duration and start/finish dates. I created a couple of helper columns (one calculating 50% & one calculating 75% of the duration as a number) to attempt to get the formula down, but I'm not having any luck. eg. if a task that has a 10 day duration is only 25% complete and there is 2 days left it would be marked "Red".

The criteria I want to use is:

• If the start date is in the future and if the % complete is 0 don't show any color ball
• If today is less than 50% of the task duration and % complete is less than 50% show green
• If today is more than 50% of the task duration and % complete is less than 50% show yellow
• If today is more than 75% of the task duration and % complete is less than 75% show red
• If % complete is 100% show blue

The formula I was trying (that is not working) is:

=IF(AND(TODAY() < ([Start Date]@row), [% Complete]@row = 0), ""), IF(AND(TODAY() < ([End Date]@row - [At Risk Helper .5]@row), [% Complete]@row < 0.5), "Green"), IF(AND(TODAY() > ([End Date]@row - [At Risk Helper .5]@row), [% Complete]@row < 0.5), "Yellow"), IF(AND(TODAY() > ([End Date]@row - [At Risk Helper .75]@row), [% Complete]@row < 0.75), "Red"), IF([% Complete]@row = 1, "Blue")))))

My columns are as follows:

• At Risk Helper .75 & At Risk Helper .5 - Text/Number
• Task Health - Symbol (R/Y/G/B)
• Task Name - Primary Column so Text/Number
• Duration - Duration
• Start Date & End Date - Date

I'd be very grateful if someone can assist!

*Edited to add the additional ')' at the end of my formula.

Thanks,

Laurie

• ✭✭✭
Options

Thank you, thank you, thank you!

That was a big oversight on my part. I actually took that and tweaked it a bit, in part because after writing it out I realized that it should have been "<=" or ">=" in some cases. I also switched around a few things to ensure that the order of operations was accurate. As an added bonus by changing it up I was able to streamline it to make it a bit more efficient. So the final formula that works as I had envisioned is as follows:

=IF([% Complete]@row = 1, "Blue", IF(AND(TODAY() < [Start Date]@row, [% Complete]@row <= 1), "", IF(AND(TODAY() <= ([End Date]@row - [At Risk Helper .5]@row), [% Complete]@row < 1), "Green", IF(AND(TODAY() >= ([End Date]@row - [At Risk Helper .5]@row), [% Complete]@row <= 0.75), "Yellow", IF(AND(TODAY() >= ([End Date]@row - [At Risk Helper .75]@row), [% Complete]@row < 1), "Red")))))

Again, thank you for your help.

Best,

Laurie

• Employee
Options

Oh wonderful! I'm so glad that I was able to help, and that you were able to create a final formula that works!!

Let me know if you have any other issues. 🙂

Cheers,

Genevieve