Cell text color change

[resoof]

Here's my situation

I have 3 colums

1. Last completed
2. 6 month expiration
3. 12 month expiration

Both the 6 month and 12 month colums have grace periods of date +1
month

The +1 month would be end of month, so if the expiration date is
08/20/2007 the grace period would be valid until 09/30/2007

I would like excel to change the text color to Blue if the current
date is beyond the expiration date but within the grace period.

I would like excel to change the text color to Red if the current date
is beyond the grace period

I currently have it set to change the color using conditional
formating if it is beyond the current date =AND(D3<>"",TODAY()-D3>0)

But i do not know how to set it up to change the color if the current
date is within the grace period and change it to a different color if
the current date is beyond the grace period.

any help would be appreciated.

Jeff

 

[Doug Glancy]

Jeff,

I was recently working on something like this. I don't always get these
right the first time, but I think this will work for you:

=IF(DATE(YEAR(D3),MOD(MONTH(D3)+2,12),1)-1>
TODAY(),DATE(YEAR(D3),MOD(MONTH(D3)+2,12),1)-1,DATE(YEAR(D3)+1,MOD(MONTH(D3)+2,12),1)-1)

It basically calculates the first day of the month after next and then
subtracts 1, giving you the last day of next month. The If and the Mod deal
with if your date is in December.

Chip Pearson has a bunch of this stuff - and may have a better answer - at:

http://www.cpearson.com/excel/DateTimeWS.htm

hth,

Doug