Date. Display colour when +1, 2 & 3 years

[tvac]

Help please. I have dates displayed in UK format '16-Jun-02' and I wish to highlight the cell with a different colour using conditional formatting when the date is 1, 2 and 3 years old. How do I do that and is conditional formatting the best way using a different colour for the three conditions?

[oldchippy]

Help please. I have dates displayed in UK format '16-Jun-02' and I wish to highlight the cell with a different colour using conditional formatting when the date is 1, 2 and 3 years old. How do I do that and is conditional formatting the best way using a different colour for the three conditions?

Hi tvac,

If you put today's date =TODAY() in cell A1,the in Format > Conditional Formatting, select formula is and enter this

=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))

then in the second condition

=DATE(YEAR(A1)+2,MONTH(A1),DAY(A1))

third

=DATE(YEAR(A1)+3,MONTH(A1),DAY(A1))

format each condition to suit

You could put =A1+365, but you may have a leap year in there somewhere!

oldchippy

[tvac]

Hi Oldchippy. Thanks for that BUT!
Same colour is displayed for all three conditions, and it is the first condition. I have tried reversing the years ie Cond 1 = 3 years etc but still the same. Even cell with date under 1 year is highlighted.
Date by the way is a permanent manual entry, and not =TODAY

[oldchippy]

Hi Oldchippy. Thanks for that BUT!
Same colour is displayed for all three conditions, and it is the first condition. I have tried reversing the years ie Cond 1 = 3 years etc but still the same. Even cell with date under 1 year is highlighted.
Date by the way is a permanent manual entry, and not =TODAY

Sorry about that, I should have tried it first, OK how about this

Condition 1 Cell Value is > less than > =TODAY()-1095
Condition 2 Cell Value is > less than > =TODAY()-730
Condition 2 Cell Value is > less than > =TODAY()-365

This should help

oldchippy

[tvac]

Spot on oldchippy! Thanks for your help.