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 are: less than 7 days is green; greater than 7 days, less than 14 is orange; greater than 14 days is red.
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$1>14 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?
Bruce
The older I get, the better I used to be.
USA
in conditional formatting:
=AND(B1-A1<8,B1-A1>0) for less than 7
=AND(B1-A1>7,B1-A1<15) for 7 to 14
=B1-A1>14 for greater than 14.
this is assuming b is always greater than a. if not, you can make it the absolute value.
Bruce:
Works Beautifully! Thanks.
-John M.
Can anyone tell me how to copy this same formula/condition for the remaining 200 cells?
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 Special>Formats
Good Luck
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks