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!