Traffic Light Formula Based on Date and % Complete

Liane Giles LeBlanc

Hello,

I am trying to create a health status formula using the traffic light: Red, Yellow, Green Blue symbols. I would like the status to update based on the Deadline as well as the % Complete. The second set of conditions within the formula will not return a result. Here is the formula:

=IF([% Complete]@row = 1, "Blue", IF([Start Date]@row > TODAY(), "Green", IF(Deadline@row < TODAY(14), IF([% Complete]@row <= 0.25, "Red", IF([% Complete]@row <= 0.5, "Yellow", "Green", IF(Deadline@row = TODAY(), IF([% Complete]@row >= 0.75, "Green", IF(Deadline@row < TODAY(), "Red", IF(Deadline@row > TODAY(14), IF([% Complete]@row <= 0.25, "Yellow", IF([% Complete]@row >= 0.5, "Green", "Green")))))))))))

Every IF statement after Deadline@row < TODAY(14) does not return data. I would love some help with whatever I'm doing wrong.

Thanks,

Liane

Manuel Macias

Hi Liane,

Looks like your trying to test for two values in certain cases. If so, you should use the AND function. Also, there was one too many colors after [% Complete]@row <= 0.5, i figured it needed to be Yellow.

Assuming my statement above is correct your formula may work if it was as follows:

=IF([% Complete]@row = 1, "Blue", IF([Start Date]@row > TODAY(), "Green", IF(AND(Deadline@row < TODAY(14),[% Complete]@row <= 0.25), "Red", IF(AND(Deadline@row < TODAY(14),[% Complete]@row <= 0.5), "Yellow", IF(AND(Deadline@row = TODAY(),[% Complete]@row >= 0.75), "Green", IF(Deadline@row < TODAY(), "Red", IF(AND(Deadline@row > TODAY(14),[% Complete]@row <= 0.25), "Yellow", IF([% Complete]@row >= 0.5, "Green", "Green"))))))))

copy paste it in and give it a shot!

If the results aren't what you expect it could be for one of two reasons. 1.) I did not understand your work flow. 2.) the logical tests need to be arranged in a different order so that they trigger in the proper order.

Happy Trails!