How to fix multi conditional formatting issue-- dates

Question

I am attempting to color code Board Member terms based on how many years they have left. I am using conditional formatting formulas to attempt this, but something isn't working correctly.

Answer 1

The rules are in the wrong order.

Consider row 3. B3+365*3 is 10/1/25. Today is 11/x/24. The first format rule returns false. The second returns true and the pink format is applied. Stop if true is set so no more rules are evaluated and you have the wrong color since less than a year to go should be green.

You should not test for < 3 years unless the tests for < 2 years and < 1 year have both failed.

Reverse the four rules. If there is less than a year to go, apply green and stop. If not, then if less than 2 apply orange and stop. ...

Since the last rule applies no format, it can be deleted. If you need to keep it, it can be simplified to TRUE.

Answer 2

Hi,

Just checking in to see if the information was helpful. Please let us know if you would like further assistance.

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.