Terminating a formula once criteria met

Daryl Lee

Hi!

I am working on a sheet that has a basic if formula to populate the Red, Yellow, Green symbols. I want to be able to have the cell not change or have the formula run once the line is shown as "Red."

My formula is

=IF(AND([Scheduled End Date]1 < TODAY(), Status1 <> "Closed"), "Red", IF([Scheduled End Date]1 <> [Actual End Date]1, "Yellow", "Green"))

With how that is now, the status will change once the Status column is updated to "Closed," but I want it to stay red once it's red, even if the status has changed to closed.

I've thought of a work around to have different closed statuses that will automatically make them the color "Closed - Red" or "Closed - Yellow" will override the rest of the if statement, but ideally I'd like to be able to stop the formula from running once the criteria is met.

Is there any way to do that or another work around that would be more seamless?

Thank you!

Paul Newcome

You could try removing the AND function along with the criteria that states the Status is not closed.

=IF([Scheduled End Date]1 < TODAY(), "Red", IF([Scheduled End Date]1 <> [Actual End Date]1, "Yellow", "Green"))

Daryl Lee

Thank you, Paul! I actually worked out another way to do it by referencing some other cells that I have that work into what I'm trying to do. Thank you again for your input, I appreciate it!

Paul Newcome

Happy to help! 👍️