Excel and Office RAQ

Highlighting dates in Excel more than two years old.

   

Tactical solutions

Research projects

Microsoft Office

Multiple choice guessing

Services

Knowledge measurement audit

Item bank services

Item design course

Products

WaterMarker

iOTA

STASiS

ACiS

CaSelector

CaSCADE

Technical articles

Excel and Office RAQ

Free Excel Add-in

Free PowerPoint Add-in

Contact

Home page

Q: I am trying to figure out how I can use conditional formatting to do the
following: If the date in row 7 is further back than today's date minus 2 years turn red.

I would appreciate any input,
 

A: Select Row 7, then from the 'Format' menu, select 'Conditional Formatting'.

Change the pulldown which reads 'Cell value is' so it reads 'Formula is' and then enter this formula in the empty field:

=A7<DATEVALUE(DAY(NOW()) & "/" & MONTH(NOW()) & "/" & YEAR(NOW())-2)

Next, hit the 'Format' button, choose your formatting and click on OK. Note that I am using Eurpoean date formats here. If you are in the US, you'll need to swap the words DAY and MONTH around - nothing else, just swap those two words.
 

Back to other RAQ topics