Forum Discussion

SeanHaggerty's avatar
SeanHaggerty
Copper Contributor
Dec 25, 2023

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))
  • Salposi's avatar
    Salposi
    Copper Contributor
    How about do this?
    =OR(AND(TODAY()-C5>=(5*365), P5>15),AND(TODAY()-C5<=(5*365), P5>10))
    • SeanHaggerty's avatar
      SeanHaggerty
      Copper Contributor
      That did it! Thanks so much.
      Looking at this formula, I can see where I was going wrong with the other one as well.
      • Salposi's avatar
        Salposi
        Copper Contributor
        To calculate the year, this function better
        =OR(AND(YEAR(TODAY())-YEAR(C5)>=5, P5>15),AND(YEAR(TODAY())-YEAR(C5)>=5, P5>10))

Resources