How do you change cell color automatically based on date

Question

I have some data and based on the amount of time someone gives me to complete the work I want to change the cell color.  I break my tiers into the following:

5 days or less       Red

6 to 14 days         Orange

15 to 29 days        Yellow

30 or more days    Blue

So I would have a date of the show and a current date.  so if Show date was 12/31/2014  and current date was 12/01/15, I would have a cell which would show 30 for the remainingg days and I would want this to automatically fill the cell blue based on this number.  If todays date was 12/27/2015 the cell would be red because only 4 days are left.  What kind of formula could I use to make this happen?

Answer 1

Hello,

you need four conditional formats, one for each color.  Select the cell. In the screenshot I selected cell A2. The formula will use the currently selected cell. 

Create a conditional format with a formula, using

=A2-TODAY()>=30

Format to blue. Note that there are no $ signs in the cell reference. A2 is the current cell and using no $ signs in the formula will look at the current cell when you apply the format to other cells.

Create three more formats with these formulas:

=A2-TODAY()<=29  -- format to yellow

=A2-TODAY()<=14  -- format to orange

=A2-TODAY()<=5 -- format to red.

In the Rules Manager the rules should be in this order:

You can use the "Applies To" fields in this dialog to select the range for which the format should be applied, or you can copy the formatted cell and use Paste Special > Formats to copy the conditional format to other cells. 

Let me know if that works for you.

Answer 2

Hi,

Try this

1. Suppose Date of show is in cell A2 and Today's date is in cell B2
2. In cell C2, enter this formula =A2-B2
3. Click on cell C2 and go to Home > Conditional formatting > New Rule > Use a formula to determine which cells to format
4. In the Formula bar there, enter this formula

=C2<=5

5. Click on Format > Fill > Red > OK > Apply
6. Repeat the steps above for the other conditions as well

The formulas would be:

=AND(C2>=6,C2<=14)

=AND(C2>=15,C2<=29)

=C2>=30

Hope this helps.

Answer 3

Cells only turning red

Answer 4

Hello,

change the order of the rules, so the red rule gets evaluated last.