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

=A17TODAY()+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

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 "NumberDate*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

=A17TODAY()+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


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=491626

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

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 Im 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. Ive asked 5 people to no avail for the passed 3 days. Thank
you-J

"Kitfox" wrote:

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

Hi

Select your range in column B where you want the formatting to apply -
say B2:B100
FormatConditional Formattinguse dropdown to select Formula Is
Formula =AND(B2<"",TODAY()-B20)
Format FontRed

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


--
Regards

Roger Govier


"j" wrote in message
...
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

"Kitfox" wrote:

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