Use Conditional Format to Highlight upcoming due dates

[Arwen_B]

Hello,

I'm a bit struggling to find the right formula for my problem.

I have this big Excel list with tables. In the first two coloms, you find the name of the personnel. In the third colom, you can find a checkbox. The other coloms (starting from E) you can find in a table the dates from when the person got this done. 

Example: Arwen B got a license at 28/04/2016 for a car. 

I would like to use conditional formatting to find a formula where:

* after 4 years and 8 months the date turns orange

* after 5 years the date turns red.

I used these formulas: 

* =TODAY()-EDATE(E3,54)>=0

* =DATEDIF(A1,TODAY(),"m")/12>=4.5

But these don't work. I always get:

There's a problem with this formula. Not trying to type a formula? use apos.........

Can someone help?

[#2 · Accepted solution]

@Arwen_B 

Select the cells with the dates.

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

Select 'Format only cells that contain'.

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

Select 'less than or equal to' from the second drop down.

In the box next to it, enter the formula

=EDATE(TODAY(),-54)

If you use comma as decimal separator, change the formula to

=EDATE(TODAY();-54)

In Dutch:

=ZELFDE.DAG(VANDAAG();-54)

Click Format...

Activate the Fill tab.

Select orange.

Click OK, then click OK again.

Repeat these steps, but with the formula

=EDATE(TODAY(),-60)

or if you use comma as decimal separator

=EDATE(TODAY();-60)

and red as fill color.

[#3]

It works, thank u!!