SOLVED

Conditional formatting formula using 2 criteria, for 2 different limits

Copper Contributor

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!

 

3 Replies
best response confirmed by SeanHaggerty (Copper Contributor)
Solution
How about do this?
=OR(AND(TODAY()-C5>=(5*365), P5>15),AND(TODAY()-C5<=(5*365), P5>10))
That did it! Thanks so much.
Looking at this formula, I can see where I was going wrong with the other one as well.
To calculate the year, this function better
=OR(AND(YEAR(TODAY())-YEAR(C5)>=5, P5>15),AND(YEAR(TODAY())-YEAR(C5)>=5, P5>10))
1 best response

Accepted Solutions
best response confirmed by SeanHaggerty (Copper Contributor)
Solution
How about do this?
=OR(AND(TODAY()-C5>=(5*365), P5>15),AND(TODAY()-C5<=(5*365), P5>10))

View solution in original post