You're going to struggle with that one because you haven't said how long you want the highlighting to show. From the formula I guess each cell in column G is going to change once a year, so how long after that change has happened do you want to stop highlighting?


You could conditionally highlight those cells which have changed since a certain date of your choosing; in the attached I've put such a date in cell K1 and conditional formatting in column G. Change the date manually in K1 and see different cells being highlighted yellow.


You might want to highlight those cells which have changed in the last 90 days, in which case instead of manually adjusting cell K1, put a formula in there such as:
=TODAY()-90


Of course, if you're sure you'll always want only to see changes in the last ninety days, you can incorporate that into the conditional formatting formula:
=(DATEDIF($H2,TODAY()-90,"Y")+I2)<>$G2
and dispense with the K1 cell.


In column N, I've put the formulae I used for conditional formatting. It's just for your information, they should be deleted since they're not needed.



Note that none of this will highlight changes due to a change in column H and/or I, only changes due to the passage of time. You'll need VBA (macros) to do that.

Attached Files

msofficeForums42335Test Excel Date.xlsx (14.2 KB, 9 views)