conditional format by comparing to current date

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

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,

Steve

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" wrote:

>
> 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,
>
> Steve
>
>
> --
> SteveG
> ------------------------------------------------------------------------
> SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
> View this thread: http://www.excelforum.com/showthread...hreadid=491626
>
>

Chris,

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

HTH

Cheers,

Steve

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

"SteveG" wrote:

>
> Chris,
>
> My cells were formatted the same and I entered in that date and it
> turned the correct color. When you entered in the formulas for the
> conditional format, did you use the format painter to carry it down
> your list? By default, when entering in the formulas for conditional
> formatting, if you select the cell using your mouse pointer, it
> automatically makes the references absolute so in my example A17 would
> appear as $A$17. When you use the format painter to carry it to the
> other cells, it will always refer to A17 if that is the case. When you
> enter the formula and this happens, highlight the whole formula and hit
> F4 3 times to remove all $. That way when you carry the format, it
> will change the reference correctly.
>
> HTH
>
> Cheers,
>
> Steve
>
>
> --
> SteveG
> ------------------------------------------------------------------------
> SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
> View this thread: http://www.excelforum.com/showthread...hreadid=491626
>
>