| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Range Conditional Formatting
Help! I can't get the following to work & I haven't been able to find the
solution in this discussion group. A1 = 1/16/08 B1 = 2/6/08 I want C1 to be green if it contains a date between the dates in A1 & B1 inclusive. I want C1 to be red if it contains a date that is not between the dates in A1 & B1 -- -Lynn F. PMP |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Range Conditional Formatting
Select cell C1
Goto FormatConditional Formatting Condition 1 Formula Is: =AND(COUNT(A1,B1)=2,C1=A1,C1<=B1) Click the Format button Select the desired style(s) OK Click the Add button Condition 2 Formula Is: =AND(COUNT(A1,B1)=2,OR(C1<A1,C1B1)) Click the Format button Select the desired style(s) OK out -- Biff Microsoft Excel MVP "Lynn" wrote in message ... Help! I can't get the following to work & I haven't been able to find the solution in this discussion group. A1 = 1/16/08 B1 = 2/6/08 I want C1 to be green if it contains a date between the dates in A1 & B1 inclusive. I want C1 to be red if it contains a date that is not between the dates in A1 & B1 -- -Lynn F. PMP |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Range Conditional Formatting
How does the COUNT function apply here?
-- -Lynn F. PMP "T. Valko" wrote: Select cell C1 Goto FormatConditional Formatting Condition 1 Formula Is: =AND(COUNT(A1,B1)=2,C1=A1,C1<=B1) Click the Format button Select the desired style(s) OK Click the Add button Condition 2 Formula Is: =AND(COUNT(A1,B1)=2,OR(C1<A1,C1B1)) Click the Format button Select the desired style(s) OK out -- Biff Microsoft Excel MVP "Lynn" wrote in message ... Help! I can't get the following to work & I haven't been able to find the solution in this discussion group. A1 = 1/16/08 B1 = 2/6/08 I want C1 to be green if it contains a date between the dates in A1 & B1 inclusive. I want C1 to be red if it contains a date that is not between the dates in A1 & B1 -- -Lynn F. PMP |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Range Conditional Formatting
It's making sure that there are 2 dates entered in both A1 and B1.
Depending on your use you may not need it in there but it won't hurt anything. For example, with condition 1, if A1 is empty but both B1 and C1 contain certain dates the format will be applied. With condition 2, if B1 is empty but both A1 and C1 conatin certain dates the format will be applied. I don't think you would want any format applied unless you have all 3 dates entered. -- Biff Microsoft Excel MVP "Lynn" wrote in message ... How does the COUNT function apply here? -- -Lynn F. PMP "T. Valko" wrote: Select cell C1 Goto FormatConditional Formatting Condition 1 Formula Is: =AND(COUNT(A1,B1)=2,C1=A1,C1<=B1) Click the Format button Select the desired style(s) OK Click the Add button Condition 2 Formula Is: =AND(COUNT(A1,B1)=2,OR(C1<A1,C1B1)) Click the Format button Select the desired style(s) OK out -- Biff Microsoft Excel MVP "Lynn" wrote in message ... Help! I can't get the following to work & I haven't been able to find the solution in this discussion group. A1 = 1/16/08 B1 = 2/6/08 I want C1 to be green if it contains a date between the dates in A1 & B1 inclusive. I want C1 to be red if it contains a date that is not between the dates in A1 & B1 -- -Lynn F. PMP |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Range Conditional Formatting
I don't think you would want any format applied unless you have all 3 dates
entered. With that in mind then we should change the formulas if you're formatting the fill color: Condition 1: =AND(COUNT(A1:C1)=3,C1=A1,C1<=B1) Condition 2: =AND(COUNT(A1:C1)=3,OR(C1<A1,C1B1)) If you're formatting the font color then the original formulas will be ok. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... It's making sure that there are 2 dates entered in both A1 and B1. Depending on your use you may not need it in there but it won't hurt anything. For example, with condition 1, if A1 is empty but both B1 and C1 contain certain dates the format will be applied. With condition 2, if B1 is empty but both A1 and C1 conatin certain dates the format will be applied. I don't think you would want any format applied unless you have all 3 dates entered. -- Biff Microsoft Excel MVP "Lynn" wrote in message ... How does the COUNT function apply here? -- -Lynn F. PMP "T. Valko" wrote: Select cell C1 Goto FormatConditional Formatting Condition 1 Formula Is: =AND(COUNT(A1,B1)=2,C1=A1,C1<=B1) Click the Format button Select the desired style(s) OK Click the Add button Condition 2 Formula Is: =AND(COUNT(A1,B1)=2,OR(C1<A1,C1B1)) Click the Format button Select the desired style(s) OK out -- Biff Microsoft Excel MVP "Lynn" wrote in message ... Help! I can't get the following to work & I haven't been able to find the solution in this discussion group. A1 = 1/16/08 B1 = 2/6/08 I want C1 to be green if it contains a date between the dates in A1 & B1 inclusive. I want C1 to be red if it contains a date that is not between the dates in A1 & B1 -- -Lynn F. PMP |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Range Conditional Formatting
Unfortunately I get the following error message: "You may not use unions,
intersections, or array constants for Conditional Formatting criteria." -- -Lynn F. PMP "T. Valko" wrote: I don't think you would want any format applied unless you have all 3 dates entered. With that in mind then we should change the formulas if you're formatting the fill color: Condition 1: =AND(COUNT(A1:C1)=3,C1=A1,C1<=B1) Condition 2: =AND(COUNT(A1:C1)=3,OR(C1<A1,C1B1)) If you're formatting the font color then the original formulas will be ok. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... It's making sure that there are 2 dates entered in both A1 and B1. Depending on your use you may not need it in there but it won't hurt anything. For example, with condition 1, if A1 is empty but both B1 and C1 contain certain dates the format will be applied. With condition 2, if B1 is empty but both A1 and C1 conatin certain dates the format will be applied. I don't think you would want any format applied unless you have all 3 dates entered. -- Biff Microsoft Excel MVP "Lynn" wrote in message ... How does the COUNT function apply here? -- -Lynn F. PMP "T. Valko" wrote: Select cell C1 Goto FormatConditional Formatting Condition 1 Formula Is: =AND(COUNT(A1,B1)=2,C1=A1,C1<=B1) Click the Format button Select the desired style(s) OK Click the Add button Condition 2 Formula Is: =AND(COUNT(A1,B1)=2,OR(C1<A1,C1B1)) Click the Format button Select the desired style(s) OK out -- Biff Microsoft Excel MVP "Lynn" wrote in message ... Help! I can't get the following to work & I haven't been able to find the solution in this discussion group. A1 = 1/16/08 B1 = 2/6/08 I want C1 to be green if it contains a date between the dates in A1 & B1 inclusive. I want C1 to be red if it contains a date that is not between the dates in A1 & B1 -- -Lynn F. PMP |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Range Conditional Formatting
Excel maintains dates as numbers. The COUNT function is verifying that there
are 2 numbers, one in A1 and one in B1 and if there are, it compares C1 to see if it is in range of the numbers (dates) in A1 and B1. Tyro "Lynn" wrote in message ... How does the COUNT function apply here? -- -Lynn F. PMP "T. Valko" wrote: Select cell C1 Goto FormatConditional Formatting Condition 1 Formula Is: =AND(COUNT(A1,B1)=2,C1=A1,C1<=B1) Click the Format button Select the desired style(s) OK Click the Add button Condition 2 Formula Is: =AND(COUNT(A1,B1)=2,OR(C1<A1,C1B1)) Click the Format button Select the desired style(s) OK out -- Biff Microsoft Excel MVP "Lynn" wrote in message ... Help! I can't get the following to work & I haven't been able to find the solution in this discussion group. A1 = 1/16/08 B1 = 2/6/08 I want C1 to be green if it contains a date between the dates in A1 & B1 inclusive. I want C1 to be red if it contains a date that is not between the dates in A1 & B1 -- -Lynn F. PMP |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Range Conditional Formatting
Use conditional formatting.
Tyro "Lynn" wrote in message ... Help! I can't get the following to work & I haven't been able to find the solution in this discussion group. A1 = 1/16/08 B1 = 2/6/08 I want C1 to be green if it contains a date between the dates in A1 & B1 inclusive. I want C1 to be red if it contains a date that is not between the dates in A1 & B1 -- -Lynn F. PMP |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Range Conditional Formatting
If I understood your problem correctly, highlight C1 and setup the CF by
selecting 'between' then the two ranges A1 and B1 then format as green. Click Add and this time select 'Not between' from the dropdown list then click on both A1 and B1 consecutively then highlight Red. H.T.H. -- when u change the way u look @ things, the things u look at change. "Lynn" wrote: Help! I can't get the following to work & I haven't been able to find the solution in this discussion group. A1 = 1/16/08 B1 = 2/6/08 I want C1 to be green if it contains a date between the dates in A1 & B1 inclusive. I want C1 to be red if it contains a date that is not between the dates in A1 & B1 -- -Lynn F. PMP |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Range Conditional Formatting
Tried 'between' and 'not between' - that seemed the logical choice but it
didn't work. Is there a problem using them with date ranges? -- -Lynn F. PMP "sahafi" wrote: If I understood your problem correctly, highlight C1 and setup the CF by selecting 'between' then the two ranges A1 and B1 then format as green. Click Add and this time select 'Not between' from the dropdown list then click on both A1 and B1 consecutively then highlight Red. H.T.H. -- when u change the way u look @ things, the things u look at change. "Lynn" wrote: Help! I can't get the following to work & I haven't been able to find the solution in this discussion group. A1 = 1/16/08 B1 = 2/6/08 I want C1 to be green if it contains a date between the dates in A1 & B1 inclusive. I want C1 to be red if it contains a date that is not between the dates in A1 & B1 -- -Lynn F. PMP |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Range Conditional Formatting
Perhaps a good read of a book by an author such as John Walkenbach
http://j-walk.com/ss on Excel on how Excel maintains dates and times and other things, $40 would be informative. It's quite easy reading. Tyro "Lynn" wrote in message ... Help! I can't get the following to work & I haven't been able to find the solution in this discussion group. A1 = 1/16/08 B1 = 2/6/08 I want C1 to be green if it contains a date between the dates in A1 & B1 inclusive. I want C1 to be red if it contains a date that is not between the dates in A1 & B1 -- -Lynn F. PMP |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| conditional formatting for cell date to equal today's date | Excel Worksheet Functions | |||
| Conditional formatting and Highlighting Date Range | Excel Discussion (Misc queries) | |||
| Conditional formatting and Highlighting Date Range | Excel Discussion (Misc queries) | |||
| Conditional formatting based on date range | Excel Discussion (Misc queries) | |||
| How do I set a date range for conditional formatting in a macro? | Excel Worksheet Functions | |||
Hybrid Mode
