 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / March 2008Date 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  Signature-Lynn F. PMP Select cell C1 Goto Format>Conditional 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,C1>B1)) Click the Format button Select the desired style(s) OK out SignatureBiff Microsoft Excel MVP > Help! I can't get the following to work & I haven't been able to find the > solution in this discussion group. [quoted text clipped - 4 lines] > I want C1 to be red if it contains a date that is not between the dates in > A1 & B1 How does the COUNT function apply here? Signature-Lynn F. PMP > Select cell C1 > Goto Format>Conditional Formatting [quoted text clipped - 20 lines] > > I want C1 to be red if it contains a date that is not between the dates in > > A1 & B1 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. SignatureBiff Microsoft Excel MVP > How does the COUNT function apply here? > [quoted text clipped - 25 lines] >> > in >> > A1 & B1 >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,C1>B1)) If you're formatting the font color then the original formulas will be ok. SignatureBiff Microsoft Excel MVP > It's making sure that there are 2 dates entered in both A1 and B1. > [quoted text clipped - 39 lines] >>> > dates in >>> > A1 & B1 Unfortunately I get the following error message: "You may not use unions, intersections, or array constants for Conditional Formatting criteria." Signature-Lynn F. PMP > >I don't think you would want any format applied unless you have all 3 dates > >entered. [quoted text clipped - 53 lines] > >>> > dates in > >>> > A1 & B1 Hmmm... That's odd! None of the formulas I suggested contain any of those. Post the *exact* formula you tried that causes that message. SignatureBiff Microsoft Excel MVP > Unfortunately I get the following error message: "You may not use unions, > intersections, or array constants for Conditional Formatting criteria." [quoted text clipped - 62 lines] >> >>> > dates in >> >>> > A1 & B1 I figured it out! I had an extra set of parenthesis. Thank you so much for your help! Signature-Lynn F. PMP > Hmmm... > [quoted text clipped - 68 lines] > >> >>> > dates in > >> >>> > A1 & B1 You're welcome! SignatureBiff Microsoft Excel MVP >I figured it out! I had an extra set of parenthesis. Thank you so much >for [quoted text clipped - 78 lines] >> >> >>> > dates in >> >> >>> > A1 & B1 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 > How does the COUNT function apply here? > [quoted text clipped - 25 lines] >> > in >> > A1 & B1 Use conditional formatting. Tyro > Help! I can't get the following to work & I haven't been able to find the > solution in this discussion group. [quoted text clipped - 4 lines] > I want C1 to be red if it contains a date that is not between the dates in > A1 & B1 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. Signaturewhen u change the way u look @ things, the things u look at change. > Help! I can't get the following to work & I haven't been able to find the > solution in this discussion group. [quoted text clipped - 4 lines] > I want C1 to be red if it contains a date that is not between the dates in > A1 & B1 Tried 'between' and 'not between' - that seemed the logical choice but it didn't work. Is there a problem using them with date ranges? Signature-Lynn F. PMP > 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 [quoted text clipped - 11 lines] > > I want C1 to be red if it contains a date that is not between the dates in > > A1 & B1 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 > Help! I can't get the following to work & I haven't been able to find the > solution in this discussion group. [quoted text clipped - 4 lines] > I want C1 to be red if it contains a date that is not between the dates in > A1 & B1 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.