Trying to Get traffic light to work with two different criteria

Kfairclo87

The part I'm having issue with is, I'm trying to get the traffic light to turn yellow if either the termination date is equal or less than 120 days out but greater than or greater than or equal to 60 days out but also look to see if the Anniversary date is less than or equal to 60 days out.

I can get the individual statements to work separately just not together.

=IF(ISBLANK([Termination Date]@row ), "Green", IF([Termination Date]@row >= TODAY(120), "Green", IF(OR(AND([Termination Date]@row >= TODAY(60), [Termination Date]@row <= TODAY(120)), AND([Next Eff. Date Anniversary]@row >= TODAY(), [Next Eff. Date Anniversary]@row <= TODAY(60))), "Yellow", IF([Termination Date]@row < TODAY(60), "Red"))))

Paul Newcome

Try this:

=IF(OR([Termination Date]@row = "", [Termination Date]@row >= TODAY(120)), "Green", IF([Termination Date]@row <= TODAY(60), "Red", IF(AND([Next Eff. Date Anniversary]@row >= TODAY(), [Next Eff. Date Anniversary]@row <= TODAY(60)), "Yellow"))

Kfairclo87

Thank you Paul for the formula. I need it to look for both termination date and Anniversary date if possible.

Paul Newcome

Termination Date is already covered by doing the red and green first. By default, if it makes it past the red and green arguments, it must be between 60 and 120 days in the future. Since that is the case, we do not need to repeat the logic which in turn simplifies the formula.

Basically, if it is less than 120 days in the future, it will not output green. Then, if it then is greater than 60 days in the future, it also will not output red and will continue to move through the formula. By skipping green and red arguments because they are false, we can assume the Termination date is where we need it to be for yellow and then only need to evaluate for the anniversary.

If you haven't already, go ahead and give the formula a try. If you have already and it is not working as expected, please let me know what it is doing and (if possible) provide some screenshots for context.