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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!