Conditional Formatting w/ Dates
 

I would like to format a set of cells on a condition using dates. For
example, in cell A1 I have 5/23/05. In cell B1 I have 6/19/05. In cell
C1 I want to have the condition dependent upon the difference of cells
A1 and B1. How do I subtract the dates to give me the condition I am
looking for. Let us say the conditions a less than 7 days is green;
greater than 7 days, less than 14 is orange; greater than 14 days is
red.


--
John F.M.
------------------------------------------------------------------------
John F.M.'s Profile: http://www.excelforum.com/member.php...o&userid=23782
View this thread: http://www.excelforum.com/showthread...hreadid=375215

Hi, John. You need to use the three conditions of CF.

Set your first condition to Formula Is: =$B$1-$A$1<=7 and set the
Pattern fill color to Green

Set your Second condition to Formula Is: =AND((B1-A1)7,(B1-A1)<15)
set the fill to Yellow

Third condition to Formula Is: =$B$1-$A$114 with fill to Red. Be
mindful of the text color that is used with the fill colors, as some
work better than others with each color.

Does this work for you?


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=375215

in conditional formatting:

=AND(B1-A1<8,B1-A10) for less than 7

=AND(B1-A17,B1-A1<15) for 7 to 14

=B1-A114 for greater than 14.

this is assuming b is always greater than a. if not, you can make it
the absolute value.


--
tkaplan
------------------------------------------------------------------------
tkaplan's Profile: http://www.excelforum.com/member.php...o&userid=22987
View this thread: http://www.excelforum.com/showthread...hreadid=375215

Bruce:

Works Beautifully! Thanks.

-John M.


--
John F.M.
------------------------------------------------------------------------
John F.M.'s Profile: http://www.excelforum.com/member.php...o&userid=23782
View this thread: http://www.excelforum.com/showthread...hreadid=375215

Can anyone tell me how to copy this same formula/condition for the
remaining 200 cells?


--
John F.M.
------------------------------------------------------------------------
John F.M.'s Profile: http://www.excelforum.com/member.php...o&userid=23782
View this thread: http://www.excelforum.com/showthread...hreadid=375215

Assumption: you will be copying this CF down column C, referring to
columns A and B as in the original formatting.... That is, A2 and B2 to
calculate C2, etc.

First, you will need to edit your formulas in the Conditional Format in
C1 to remove any fixed references (i.e. '$') so that =$B$1-$A$1<=7
becomes =B1-A1<=7 and so on...

then simply copy this cell, select your desired range (C2:C200?) and
Paste SpecialFormats

Good Luck


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=375215