Forum Discussion
Solved
How to Use Conditional Formatting to Highlight Past Date in Cell?
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!
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.
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.
