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

Laurie A

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

Genevieve P.

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

Genevieve P.

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

Laurie A

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

Genevieve P.

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