 |
 | 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 / August 2005How do I calculate if a date is in a certain time frame?
I am tracking dates of service and I need to be able to see if dates fall within a certain time frame. column F Column H row 1 (date 1) (date 2) column H column J column L (Client 1) date date date I want to see if any of the clients dates fall between date 1 and date 2. If any fall between these dates then count as 1 or true. If the dates are before date 1 or after date 2, then I don't want to count them at all. The problem is if I use > date 1 and < date 2 then it counts everything before date 1 as well. Please help me. Hi! See this: http://tinyurl.com/aml9c Biff >I am tracking dates of service and I need to be able to see if dates fall > within a certain time frame. [quoted text clipped - 10 lines] > problem is if I use > date 1 and < date 2 then it counts everything before > date 1 as well. Please help me. I tried all 3 suggestions, but they didn't work. > Hi! > [quoted text clipped - 18 lines] > > problem is if I use > date 1 and < date 2 then it counts everything before > > date 1 as well. Please help me. Are you sure that the dates are true Excel dates? True Excel dates are really numbers with a special format. Check the formats for your date cells. If you select a date cell then right click and choose Format Cells what format does it show as being used? Biff >I tried all 3 suggestions, but they didn't work. > [quoted text clipped - 24 lines] >> > before >> > date 1 as well. Please help me. It is a date cell. It says Type : date > Are you sure that the dates are true Excel dates? > [quoted text clipped - 33 lines] > >> > before > >> > date 1 as well. Please help me. OK, maybe if you give some examples of what to count and when... For example.... F1 = 1/1/2005 H1 = 1/2/2005 H5 = 1/1/2005 J5 = 1/10/2005 L5 = blank (empty cell) What result would you expect from that example? Based on my understanding of what you want I would think the result should be 1. Also, you didn't really say whether you actually meant >= and <= the dates in F1 and H1. You said "within" and a literal reading of that would exclude both of the dates in my example. Biff > It is a date cell. It says Type : date > [quoted text clipped - 39 lines] >> >> > before >> >> > date 1 as well. Please help me. The dates in F1 and H1 are a 2 month span. F1 =TODAY()-365 H1 =TODAY()-305. H5 is 6/17/04 J5 is 1/11/05 L5 is 10/18/04 The only cell which would count is L5. H5 is too long ago, and J5 is not long enough. so... if I say >=$F$1 it counts J5, and if I say <=$H$1 it counts H5. I need it to count only the dates which fall in between F1 and H1. I don't know how to combine the equation to make it fit Both criteria at the same time. How DO you learn all this stuff? > OK, maybe if you give some examples of what to count and when... > [quoted text clipped - 58 lines] > >> >> > before > >> >> > date 1 as well. Please help me. Hi! >The only cell which would count is L5. H5 is too long ago, and J5 is not >long enough. so... if I say >=$F$1 it counts J5, and if I say <=$H$1 it >counts H5. OK, that's where you're not understanding how the formula works. The dates have to meet both of those conditions to be counted. The date has to be >=F1 AND <=H1. F1 = 8/27/2004 H1 = 11/26/2004 H5 = 6/17/2004 is NOT >F1 but IS <H1 So this is how it gets evaluated: FALSE * TRUE = 0 (not counted) And the formula does the same thing for the other dates so that you have an array that looks like this: H5 = 6/17/2004 = FALSE * TRUE = 0 J5 = 1/11/2005 = TRUE * FALSE = 0 L5 = 10/18/2004 = TRUE * TRUE = 1 So, the only date counted is in L5. This formula is a little more robust in that it accounts for empty cells: =IF(COUNT(H5,J5,L5)>0,IF(SUMPRODUCT(--(MOD(COLUMN(H5:L5),2)=0),--(H5:L5>=F1),--(H5:L5<=H1)),1,0),0) Biff > The dates in F1 and H1 are a 2 month span. > F1 =TODAY()-365 H1 =TODAY()-305. [quoted text clipped - 76 lines] >> >> >> > before >> >> >> > date 1 as well. Please help me. Biff, it still counts dates prior to F1. > Hi! > [quoted text clipped - 110 lines] > >> >> >> > before > >> >> >> > date 1 as well. Please help me. Can you send me a copy of the file? If so, let me know how to contact you. Biff > Biff, it still counts dates prior to F1. > [quoted text clipped - 128 lines] >> >> >> >> > before >> >> >> >> > date 1 as well. Please help me. e-mail - see my profile > Can you send me a copy of the file? If so, let me know how to contact you. > [quoted text clipped - 132 lines] > >> >> >> >> > before > >> >> >> >> > date 1 as well. Please help me. [display name]04@aol.com > > Can you send me a copy of the file? If so, let me know how to contact you. > > [quoted text clipped - 132 lines] > > >> >> >> >> > before > > >> >> >> >> > date 1 as well. Please help me. >I am tracking dates of service and I need to be able to see if dates fall >within a certain time frame. [quoted text clipped - 9 lines] >problem is if I use > date 1 and < date 2 then it counts everything before >date 1 as well. Please help me. With the range of dates that you are checking named "rng" (you can substitute a cell reference for this): =MIN(1,COUNTIF(rng,">=" & date1) - COUNTIF(rng,">" & date2)) date1 and date2 may be cell references also. --ron With the range of dates that you are checking named "rng" ----How do I do this? > >I am tracking dates of service and I need to be able to see if dates fall > >within a certain time frame. [quoted text clipped - 18 lines] > > --ron I tried your suggestion but it is still counting 1 when it should be 0, This is what I typed: =MIN(1,COUNTIF(H5:L5,">=" &$F$1) - COUNTIF(H5:L5,"<=" &$H$1)) What am I doing wrong? > >I am tracking dates of service and I need to be able to see if dates fall > >within a certain time frame. [quoted text clipped - 18 lines] > > --ron >I tried your suggestion but it is still counting 1 when it should be 0, >This is what I typed: >=MIN(1,COUNTIF(H5:L5,">=" &$F$1) - COUNTIF(H5:L5,"<=" &$H$1)) >What am I doing wrong? Uh, you are NOT using the equation I posted. You have the comparison operator incorrect in your second COUNTIF function. Assuming F1 contains your earlier date, and H1 contains your later date. Change yours to: =MIN(1,COUNTIF(H5:L5,">=" &$F$1) - COUNTIF(H5:L5,">" &$H$1)) --ron >=MIN(1,COUNTIF(H5:L5,">=" &$F$1) - COUNTIF(H5:L5,">" &$H$1)) Hmmm... If that works then so should: =IF(SUMPRODUCT(--(MOD(COLUMN(H5:L5),2)=0),--(H5:L5>=F1),--(H5:L5<=H1)),1,0) I was accounting for the non-contiguous range and the possibilty of other numeric data within the range, but even if you leave that out: =IF(SUMPRODUCT(--(H5:L5>=F1),--(H5:L5<=H1)),1,0) That should also work. I don't get it! Biff >>I tried your suggestion but it is still counting 1 when it should be 0, >>This is what I typed: [quoted text clipped - 13 lines] > > --ron >>=MIN(1,COUNTIF(H5:L5,">=" &$F$1) - COUNTIF(H5:L5,">" &$H$1)) > [quoted text clipped - 12 lines] > >I don't get it! When Pe66les posted the variation of *my* formula that he actually tried, it was incorrect. Perhaps the same thing was going on with the translation of yours. --ron WOOOHOOOO!!!!! I did it!!!! I used: =3-COUNTIF(H5:L5,">="&$H$1)-COUNTIF(H5:L5,"<="&$F$1) It worked!!!!! Thank You so much for helping me figure out what I needed to do!!! > I am tracking dates of service and I need to be able to see if dates fall > within a certain time frame. [quoted text clipped - 9 lines] > problem is if I use > date 1 and < date 2 then it counts everything before > date 1 as well. Please help me. OK, You get A Double WOOOOOHOOOO!!!!! I corrected my mistake and your equation is much better than mine. Thank you!!!! Thank you!!! Thank you!!! > WOOOHOOOO!!!!! I did it!!!! I used: > =3-COUNTIF(H5:L5,">="&$H$1)-COUNTIF(H5:L5,"<="&$F$1) [quoted text clipped - 14 lines] > > problem is if I use > date 1 and < date 2 then it counts everything before > > date 1 as well. Please help me. |
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
|
©2009 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.