Traffic Light Formula Based on Date and % Complete



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.




  • Manuel Macias
    Manuel Macias ✭✭
    edited 02/12/20

    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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!