Conditional Formatting for service award

[ninjapaping]

hello! i have a column of dates the employees were hired. i want to format these when they reach 10 years as of today and every 5 years thereafter. what formula should i use in conditional formatting?

 

[ninjapaping]

NEVERMIND. I FIGURED I USED THE WRONG FORMULA "AND". SHOULD HAVE USED "OR" INSTEAD. THANK YOU @MARK858 AND @PeteWright

 

[MARK858]

The AND in

=AND((YEAR(L2)+10)=YEAR(TODAY()))

or the OR in

=OR((YEAR(L2)+10)=YEAR(TODAY()))

do nothing as there isn't a 2nd criteria

If you mean you are using the OR for multiple tests of the dates then yes that works if you are using the same format for each test

=OR(YEAR(L2)=YEAR(TODAY())-10,YEAR(L2)=YEAR(TODAY())-15,YEAR(L2)=YEAR(TODAY())-20,YEAR(L2)=YEAR(TODAY())-25)

 

[ninjapaping]

The AND in

=AND((YEAR(L2)+10)=YEAR(TODAY()))

or the OR in

=OR((YEAR(L2)+10)=YEAR(TODAY()))

do nothing as there isn't a 2nd criteria

If you mean you are using the OR for multiple tests of the dates then yes that works if you are using the same format for each test

=OR(YEAR(L2)=YEAR(TODAY())-10,YEAR(L2)=YEAR(TODAY())-15,YEAR(L2)=YEAR(TODAY())-20,YEAR(L2)=YEAR(TODAY())-25)

Yes, that's exactly it. Thanks!

 

[MARK858]

Happy it helped and welcome to the forum