Forum Discussion
SeanHaggerty
Dec 25, 2023Copper Contributor
Conditional formatting formula using 2 criteria, for 2 different limits
I am trying to create a conditional formatting highlight formula that will highlight the cell if the date in C5 is more than 5 years and P5 has a value of more than 15, or if the date in C5 is less than 5 years and P5 is more than 10.
I have gotten to the point of being able to highlight the first part using this formula, but can't figure out how to introduce the second aspect and not get an error.
=AND(TODAY()-$C$5>=(5*365),$P$5>15)
I have played around with trying to make it work with an OR function, but keep getting an error.
=AND(OR(TODAY()-C5>=(5*365), P5>15),(TODAY()-C5<=(5*365), P5>10))
Any help on where I am going wrong is really appreciated!
- How about do this?
=OR(AND(TODAY()-C5>=(5*365), P5>15),AND(TODAY()-C5<=(5*365), P5>10))
- SalposiCopper ContributorHow about do this?
=OR(AND(TODAY()-C5>=(5*365), P5>15),AND(TODAY()-C5<=(5*365), P5>10))- SeanHaggertyCopper ContributorThat did it! Thanks so much.
Looking at this formula, I can see where I was going wrong with the other one as well.- SalposiCopper ContributorTo calculate the year, this function better
=OR(AND(YEAR(TODAY())-YEAR(C5)>=5, P5>15),AND(YEAR(TODAY())-YEAR(C5)>=5, P5>10))