Adding to TODAY() in a conditional format rule

**jdog85** · 01-25-2011, 07:11 PM

Hi,
please forgive any mistakes as I am not massively proficient in this but...

I am trying to use a conditional format 3-colour scale to colour dates for when things such as insurance policies are due for renewal. I would like any cells with a date of within 2 months of today to be red, 2-3 months to be orange or whatever, and more than 3 months to be green.

I have tried to set the rule using formulas but cannot get it to work - it accepts the formulas but no colours appear.

The three formulas I used were:

1. =TODAY() - this worked fine when the other 2 were set to percentages

2. =DATE(YEAR(DATEVALUE("TODAY()")),MONTH(DATEVALUE("TODAY()"))+2,DAY(DATEVALUE("TODAY()")))

3. =DATE(YEAR(DATEVALUE("TODAY()")),MONTH(DATEVALUE("TODAY()"))+3,DAY(DATEVALUE("TODAY()")))

The only other alternitive I tried was setting cells to have those values and trying to get the formula to use them, but excel told me you cannot "use relative references in conditional formatting for colour scales".

Is it just not possible to do this? or are my formulas not doing what I think they are?

any help would be much appreciated!

**jeffreybrown** · 01-25-2011, 07:19 PM

Hello & Welcome to the Board,

Might this help...

http://www.xldynamic.com/source/xld.CF.html#due

**daddylonglegs** · 01-25-2011, 07:24 PM

You can use DATEDIF for this, i.e. for green condition

=DATEDIF(TODAY(),A1,"m")>=3

for orange

=DATEDIF(TODAY(),A1,"m")>=2

then all remaining dates should be red so you can use a formula that will identify any remaining dates not formatted by the previous 2 conditions, i.e.

=ISNUMBER(A1)

format red

see attached

Press F9 to re-generate random dates

**jdog85** · 01-26-2011, 09:42 AM

many thanks for those responses!
I don't know why but using the "use a formula to determine which cells to format" rather than the "format cells based on value" option hadn't occured to me even though I was trying to use a formula!

Many thanks again!