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.

On May 19, 5:48*pm, 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.

I am not sure how much different 2007 is from 2003 so II will throw
out this soloution and hope it helps you out. Highlight cells that
you want to change color. Click format cells\Conditional Formatting.
Chose by formula and not by cell. This is the formula that you woudl
put in if N8 was the celll that contained the date =(TODAY()-N8)<180.
Then you just have to add additional formatting and change the 180 to
360 and then to 360 to go red.

I hope this helps,
Jay

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.