make cells change color based on date

[ahitchcox]

I have a very large excel spreadsheet (excel 2016) that is used to track a bunch of gear that is issued out to individual people. Basically I have a column with serial number, a column with inspection dates, a column with issued dates, and a column with names. The gear I have in this log has to be inspected every three years but a lot of these equipment has different inspection dates.

I'm trying to make it so excel will automatically make the persons name turn yellow when it's been 2 years since the last inspection and then turn red when it's 2.5 years after the last inspection.

Is there a way to do this?

 

[Scott T]

You can use Conditional Formatting.

You will need one rule for each color
Select the cells with the name
Select Conditional Formatting
New Rule
Use a formula to determine which cells to format
for yellow use change A2 to the cell that has the last inspection date. note: Excel will update the cell referenced as it goes to other rows

=TODAY()-A2>730

select your formatting then press OK

repeat for red but use

=TODAY()-A2>1095