Automatically highlight future dates once they reach expiration.

[Elnene1]

Hello all,

I have an equipment maintenance register that lists all equipment in our shop that requires maintenance every 6 months. There are different dates but all dates need to be updated every six months. If you look below, I want the dates in F5-F82 to automatically change to the color red 2 days before the six month expiration. Is this possible? Please help! Thank you!

 

[Toadstool]

Hi Elnene1,

You can do that with Conditional Formatting

Elnene1.xlsx
	D	E	F	G	H	I
3			Date Last Completed
4	Vehicles
5			01-Jan-21
6			28-Jan-21
7	Lifting Accessories
8			23-Mar-21
9			19-Apr-21
10			16-May-21
11			12-Jun-21
12			24-Apr-21
13
14			01-Sep-21
15			28-Sep-21
16			25-Oct-21
17			28-Jan-21
18
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F5:F82	Expression	=AND(F5<>"",EDATE(F5,6)<(TODAY()-2))	text	NO

 

[Elnene1]

Hello Toadstool,

What cell do I put this formula in? I tried to put it in cell F5 but that did not work so I tried to put the formula in cell H5 and it still did not work.

 

[Toadstool]

It goes into F5 as that's the first cell of the range to check.

...then change the Applies to to =$F$5:$F$82

It will actually show more cells selected because you have the merged cells (merged cells always cause challenges with formulae).