Help with calculating health (R/Y/G/B) based on end date and % complete
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
Best Answer

Hi Laurie,
It looks like you just added in some extra closing parenthesis at the end of each IF statement, which would cause the formula to error. Try this:
=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")))))
You'll notice that I kept each IF statement open at the end, so that it can move on and read the next statement. I then closed off each IF statement only at the very end of the whole formula. I haven't actually tested it for each of your criteria, so if you're finding that this doesn't return the proper result, could you explain how you get the helper number in your two helper columns?
Thanks!
Genevieve
Answers

Hi Laurie,
It looks like you just added in some extra closing parenthesis at the end of each IF statement, which would cause the formula to error. Try this:
=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")))))
You'll notice that I kept each IF statement open at the end, so that it can move on and read the next statement. I then closed off each IF statement only at the very end of the whole formula. I haven't actually tested it for each of your criteria, so if you're finding that this doesn't return the proper result, could you explain how you get the helper number in your two helper columns?
Thanks!
Genevieve

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")))))
To answer your questions about the helper columns they were simple mathematics formulas (=Duration@row*.5 or =Duration@row*.75).
Again, thank you for your help.
Best,
Laurie

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
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 68 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!