Deadline date Conditional formatting.

[CCook82]

Hi all, I have been tasked with creating a worksheet with conditional formatting. I need to create it so if a deadline date is over 30 days before today's date the field will be green in the deadline data cell.
So if today's date is within 30 days of deadline it shows amber, or if the deadline is after or on today it shows as Red.

I hope this makes sense as it has had me scratching my head whilst trying to do the conditional formatting. Any help would be much appreciated. I created a sample Spreadsheet that I tried to work it out on.

Many Thanks.

[FDibbins]

just to check I understand...
30 days or more after today...green =C2>=E2+30
less than 30 days after today...amber =C2<E2+30
before today...red =C2<E2

You will need a new rule for each criteria, enter them in that order, they should then appear in reverse order, check the "stop if true" option

[CCook82]

Cheers Ford I'll try it out and see how it works out.

[CCook82]

Hi Ford that is all working thanks, the only thing I changed from your advice was to change E2 to today():

It was easier to replicate down using today(). Many thanks for your help

[CCook82]

Another head scratcher for myself. I now have the conditional formatting set thanks to Ford showing the relevant colours against the deadline dates, but when I replicated the cell down the empty cells filled with red.

I would like to have empty cells remain as white, is there a conditional format to set this?

Many thanks.

[FDibbins]

1. may be better to have TODAY in it's own cell, then reference it - TODAY is a dynamic formula and could slow your file down if used excessively

2. start your rules with...
=AND(C2<>"",....

to edit the formula without excel adding extra references (that always gets me) press F2 1st

[CCook82]

Cheers i'll try it out using the =AND formula.

[FDibbins]

Let me know how you make out plz