Countdown date colour change conditional formatting

[Paul_T]

Hi all,

I'm trying to conditional format a cell with a date in to change colour the closer it gets to the deadline from todays date.
For example, I have 2 weeks to go before a deadline - cell colour green.
1 week to go - cell colour yellow
3 days to go - cell colour orange
1 day to go - cell colour red
etc.

Any help would be appreciated.
Thanks,

Paul.

[Aardigspook]

Edit: ignore the formulae here - see later post for better solution


1. Select your range (the range you want to format).
2. On the Home tab of the ribbon, click 'Conditional Formatting', then 'New Rule', then 'Use a formula to determine which cells to format'.
3. In the formula box, enter this:

Please Login or Register  to view this content.

Replace A2 with the top-left cell of your range.

4. Click 'Format' then choose green fill.

5. Repeat steps 3 & 4 with this formula and yellow fill:

Please Login or Register  to view this content.

6. Repeat again with this and orange fill:

Please Login or Register  to view this content.

7. Finally repeat with this formula and red fill:

Please Login or Register  to view this content.

Doing them in this order will ensure that Excel first checks for the fortnight, then for the week, then 3 days then 1 day and colours the cells appropriately.

Hope that does what you want.

[Paul_T]

Thanks for the reply.
I can't get that to work properly, it seems there's a conflict.
I think it defaults to the lowest number and stays that colour.
Ended up with =(A2-TODAY())>7 etc. that seemed to work to a point but it looks as though you can have only 3 rules running with conditional formatting?
At least I think so as it doesn't work with a 4 date countdown.

[Aardigspook]

Sorry, that's what comes of typing something while trying to do something else at the same time and not actually checking that it works.

Here are better rules for green, yellow, orange, red fills:

Please Login or Register  to view this content.

Attached is a file showing it working.

As long as you're running Excel 2007 or later, you can have a lot more than 3 CF rules (2003 and earlier versions were limited to 3).

Again, sorry for the previous incorrect rules and I hope that this now does what you want.

[Paul_T]

Spot on. Works great!
Thanks for the help

[Aardigspook]

You're welcome, glad I could help (at the second attempt ).
If that takes care of your original question, then please take a moment to mark the thread as Solved so others know there's an answer here (instructions are in my sig (in my first post)). Thanks.