How to Use Conditional Formatting to Highlight Past Date in Cell?

[allisonmathews]

Hello!

I keep a spreadsheet with the date that our employees signed their offer acceptance letter detailing their role responsibilities. How would I create a rule that the cell would automatically highlight red ("bad") when it is two years past the date in the cell? I understand how to make rules that are based on today's date. However, each cell is different, and I want it to be two years past each different date. Thanks!

[#2 · Accepted solution]

@allisonmathews 

Select the cells with the dates.

On the Home tab of the ribbon, select Conditional Formatting > New Rule...

Select 'Format only cells that contain'.

Leave the first drop down set to 'Cell Value'.

Select 'less than' from the drop down next to it.

Enter the formula  =EDATE(TODAY(),-24)  in the box next to it.

Click Format...

Activate the Fill tab.

Select red.

Click OK, then click OK again.