conditional format by comparing to current date

[Kitfox]

I have a sheet with expiration dates entered in a column of cells. I need
the conditional formatting to automatically change the background color of
those cells to help identify expired dates. I need the colors to change from
green, which is more than 14 days in the future, to yellow, which is between
1 and 14 days out, to red which would be the current date or a past date to
indicate expiration. Can this be done and how?

I used contional formatting as follows:
Condition 1-Cell is less than="today()" Pattern is Green
Condition 2-Cell is between="today()" and "today()+14" Pattern is Yellow
Condition 3-Cell is greater than="today()+14" Pattern is Red

Please help,
Thanks, Chris

 

[SteveG]

Chris,

Try this.

Instead of Cell Value is, use the Formula Is option.


Condition 1

=A17<=TODAY() then Red

Condition 2

=A17<=TODAY()+14 then Yellow

Condition 3

=A17>TODAY()+14 then Green



Cheers,

Stev

 

[Kitfox]

Thanks, Steve. It did help, but for some reason, one of the cells turned red
and it is dated 7/18/06, so it should be green. Should I change my cell
format? It is currently set to "Number>Date>*3/14/2001". Thanks again for
the help.

Chris

 

[SteveG]

Chris,

My cells were formatted the same and I entered in that date and i
turned the correct color. When you entered in the formulas for th
conditional format, did you use the format painter to carry it dow
your list? By default, when entering in the formulas for conditiona
formatting, if you select the cell using your mouse pointer, i
automatically makes the references absolute so in my example A17 woul
appear as $A$17. When you use the format painter to carry it to th
other cells, it will always refer to A17 if that is the case. When yo
enter the formula and this happens, highlight the whole formula and hi
F4 3 times to remove all $. That way when you carry the format, i
will change the reference correctly.

HTH

Cheers,

Stev

 

[Kitfox]

Wow, Steve, you are awesome. You won't believe how much I sweated over this.
Now I can grow my hair back. Your information was key to solving the
problem. Thanks a million.

Chris

 

[j]

I have a similar question except that I have two columns. One with a start
date, the second column automatically updates to show a date 30 from the
start date so show that when that update is due. What I’m trying to do is
change the color of the updated column to red once that date is over due.
Ex. Start date is 26 Oct 2006; update reads “26 Nov 2006 and now it is 05 Dec
2006 so now “26 Nov 2006 should turn red. Anyone that can help me is highly
appreciated. I’ve asked 5 people to no avail for the passed 3 days. Thank
you-J

 

[Roger Govier]

Hi

Select your range in column B where you want the formatting to apply -
say B2:B100
Format>Conditional Formatting>use dropdown to select Formula Is
Formula =AND(B2<>"",TODAY()-B2>0)
Format Font>Red

Note : Substitute for B2 whatever is the first cell in the range you
have selected.