I have a large spreadsheet that I monitor that I need to keep tabs on dates for. What I would like to do is have text that has occurred within the last 17-22 years from the current date be highlighted. I would achieve this by using a formula in the custom conditional formatting so that data in this range is immediately apparent, I am just striking out with figuring out the correct formula.



I currently manually update a set date range but would prefer to use a floating date range that uses today's date as the point of reference (conditionally format for any day that occurred between 17-22 years before today). Similarly I would like to set up conditional formatting that looks at dates that are between today and 30 days after today instead of within the current calendar month.

I am currently using Excel 2007.

Thank you for your help!

Is it 17-22 years or days?

When days, then (2 conditional formats, let's assume the date you are checking is in column A, the 1st data row is 2):

Select the range you want the conditional formats apply to (a single column in your table, several columns, or the whole table - minus header(s)).
Open Conditional formatting, apply 2 condition formulas and according formats.
1. =AND(($A2-17)>=TODAY();($A2-22)<=TODAY())
2. =AND(($A2+30)>=TODAY();($A2)<=TODAY())

Oops it is years (old people should not stay up past midnight). Thank you!