Highlighting dates older than 4 years

[james1234]

Hello, I have an excel sheet with a list of dates, and what I'd like to know is how I can colour/highlight the dates more than four years ago, preferably to the month. I've tried to use conditional formatting to do this but failed, so any help would be greatly appreciated.

[DonkeyOte]

Assume dates are in A1:A100, with said range highlighted having selected A1 first - applying a Conditional Formula rule of:

=DATEDIF(A1,TODAY(),"y")>4

with format set as appropriate should work though the 4 years will be based on the day

If you want to disregard the day of the month then you could adjust the above accordingly, ie::

=DATEDIF(A1-DAY(A1)+1,TODAY()-DAY(TODAY())+1,"y")>4

In the above both dates (criteria and current date) are normalised to 1st of Month before duration is calculated.

Were A1 5th April 2005 the first DATEDIF formula would 4 and thus False (ie 4 not > 4) whereas the latter would return 5 and thus the cell would be formatted per the config applied.

(modify ranges to suit of course)

[james1234]

Thanks very much DonkeyOte, I'll give that a go