Select the range B2:Q41.
On the home tab, in the Styles group, click Conditional Formatting and
Manage Rules... Click New Rule...
In the dialog box, click Use a formula to determine which cells to format.
In the text box, add the following formula:
=AND(TODAY()-180<B2,TODAY()B2)
Click Format... and select your favorite shade of green.
Click OK and OK.
Click New Rule and Use a formula - again
Add the following formula:
=TODAY()-B2180
Click Format... and select your favorite shade of yellow.
Click OK and OK.
Click New Rule and Use a formula - again
Add the following formula:
=TODAY()-B2360
Click Format... and select your favorite shade of red.
Click OK and OK.
You should see them listed with red at the top and green at the bottom.
This will leave any future dates with the original format... click Ok and I
think that's what you were looking for.


"jstegall" wrote:

I have a range of dates "=$B$2:$Q$41" that I need to have the cells change to
green if the date is less than 6 months old, yellow if between 6 months and
360 days, and red if anything older than 360 days. I appreciate any help I
can get. I am using Excel 2007.