Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formatting - compare 2 dates | Excel Discussion (Misc queries) | |||
Conditional Formatting with Dates | Excel Worksheet Functions | |||
Conditional formatting with dates formula problem. | Excel Discussion (Misc queries) | |||
conditional formatting overdue dates | Excel Discussion (Misc queries) | |||
Conditional Formatting Dates | Excel Worksheet Functions |