Formula issue with B, R, Y, G Traffic Light with a Deadline and % Complete

I have been struggle with this formula to leverage the Blue, Red, Yellow Green harvey ball. Basically, If there is no deadline and the task has not been started then it should be Blue, otherwise if < current date and % Complete is not 100% then it should be RED, and YELLOW if the deadline is today and % complete is < 1 and all other conditions should be green.

Current Formula does not consider % Complete and turns red on tasks that are 100% complete: =IF(Deadline@row = "", "Blue", IF(Deadline6 < TODAY(), "Red", IF(Deadline6 = TODAY(), "Yellow", IF(Deadline6 > TODAY(), "Green"))))

Trying to consider % Complete with this revision that I am largely unsuccessful with so far and need some help:

=IF(AND([Deadline]23 = "", (Status23 = "Not Started" )), "Blue", IF(AND([Deadline]23 < TODAY(), (% Complete23 < 1)), "Red", IF(AND([Deadline]23 = TODAY(), (% Complete23 < 1)), "Yellow", IF(AND([Deadline]23 > TODAY(), (% Complete23 <= 1)), "Green"))))

Any assistance would be greatly appreciated!

-John

• ✭✭✭✭✭✭

Try something like this...

=IF(Deadline@row = "", "Blue", IF([% Complete]@row < 1, IF(Deadline@row = TODAY(), "Yellow", IF(Deadline@row < TODAY(), "Red", "Green")), "Green"))

• Hey Paul,

Thank you for the direction this but wanted to ask a follow up. On the condition Blue and Green. It want it to be Blue is no deadline and Status = Not Started and Green if 100% complete. If the task is 100% complete and has no deadline it should still be Green because the task is technically complete.

That logic is what I was struggling with and left me experiment with IF / AND clauses. Thoughts on that scenario?

I really appreciate your direction on this!

-John

• Paul,

This will work perfectly. Thank you for your help on this and from your examples I now know how to properly use a compound IF/AND clause.

Thanks again!!!

-John

• ✭✭✭✭✭✭

Happy to help. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!