Conditional formatting based on another cell

Hello,

I am creating a list of fire extinguishers and their service dates. I want the service date column to highlight when the extinguisher will need to be serviced within the next year. The issue that I am having is that there are different types of extinguishers that have different lengths of time between service.

I have them highlighting yellow for those needing service within and year and red for those past their service dates. Here's an example of my formula for those needing service within a year:

=AND(G2<=TODAY()-1825, G2>=TODAY()-2130)

Most typical extinguishers have a 6 year expiration, but there is one type that has a 5 year expiration. Is it possible for me to write a formula that will highlight after 4 years if another column in that row says K and to highlight after 5 years if another column in that row says ABC?

excel.PNG

Highlight your dates in column G (G2:G__) > Conditional Formatting > New Rule

=EDATE(G2,IF(E2="K",36,48)) <= TODAY()

Format: Fill Yellow > OK > OK


With your data in column G still highlighted, Conditional Formatting > New Rule

=EDATE(G2,IF(E2="K",48,60)) < TODAY()

Format: Fill Red > OK > OK

It is very important that you apply the rules in this order. Otherwise it will not work correctly.