I need the cells to change based on age of date.

  • Thread starter Thread starter jstegall
  • Start date Start date
J

jstegall

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 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.
Click to expand...

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()-B2>180
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()-B2>360
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.
 
Back
Top