georgeways

Hi,

I'm trying to sort out a problem that had me cracking my head for days.
I'm using excel 2003 to do conditional formatting with different dates for insurance premiums renewal.

Todays date: 19/09/2009

19-03-10
12-10-10
06-11-10
03-09-10
18-09-10
21-06-10
05-07-10
10-07-10
01-12-10
12-10-10
28-09-10
28-12-10
12-12-10
03-09-10
14-09-10
16-10-10


Scenario 1:
Im trying to make dates in cells that fall within 351 days before next year to turn green

Scenario 2:
Im trying to make dates in cells that will expire within 14 days before next year
to turn yellow.

Scenario 3:
Im trying to make dates in cells that will expire within 7 days before next year
to turn red.

pls help with the conditional formatting.

Highly appreciated

.

Mister2

Hi georgeways,

Try this - (I have assumed your renewal dates are in cells A5 to A30 - if your range is different then just selectb the appropriate range in Step 1 below):

1. Select cells A5:A30, right click, Format, Date, select the date format you prefer

2. With that range still selected, go to Format, Conditional Formatting

3. In the Conditional Formatting box select 'Formula Is' and type the following into the box:
=IF(A5<=TODAY()+7, TRUE, FALSE)
Click the 'Formatting...' button and set the text colour to red.
Click OK and OK again.
That deals with the almost-due dates

4. Go to Format, Conditional Formatting again and click the Add button

5. Select 'Formula Is' and type the following into the box:
=IF(A5>TODAY()+7, IF(A5<=TODAY()+14, TRUE, FALSE), FALSE)
Click the 'Formatting...' button and set the text colour to yellow.
Click OK and OK again.
That deals with the becoming-due dates

6. Finally, select 'Formula Is' and type the following into the box:
=IF(A5>TODAY()+14, TRUE, FALSE)
Click the 'Formatting...' button and set the text colour to green.
Click OK and OK again.
That deals with the don't-bother-yet dates

If you want to change the 'notice period' for your alerts then simply highlight that range again, go to Format, Conditional Formatting and edit the 7's and 14's to the number of days you want.

Hope that helps