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

John Gillespie

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

Paul Newcome

My apologies. I misread your request. Try this...

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

Paul Newcome

Try something like this...

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

John Gillespie

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 Newcome

My apologies. I misread your request. Try this...

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

John Gillespie

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

Paul Newcome

Happy to help. 👍️