|
How 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 "Pe66les" wrote in message ... 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. |
I tried all 3 suggestions, but they didn't work.
"Biff" wrote: Hi! See this: http://tinyurl.com/aml9c Biff "Pe66les" wrote in message ... 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. |
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 "Pe66les" wrote in message ... I tried all 3 suggestions, but they didn't work. "Biff" wrote: Hi! See this: http://tinyurl.com/aml9c Biff "Pe66les" wrote in message ... 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. |
It is a date cell. It says Type : date
"Biff" wrote: 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 "Pe66les" wrote in message ... I tried all 3 suggestions, but they didn't work. "Biff" wrote: Hi! See this: http://tinyurl.com/aml9c Biff "Pe66les" wrote in message ... 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. |
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 "Pe66les" wrote in message ... It is a date cell. It says Type : date "Biff" wrote: 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 "Pe66les" wrote in message ... I tried all 3 suggestions, but they didn't work. "Biff" wrote: Hi! See this: http://tinyurl.com/aml9c Biff "Pe66les" wrote in message ... 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. |
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? "Biff" wrote: 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 "Pe66les" wrote in message ... It is a date cell. It says Type : date "Biff" wrote: 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 "Pe66les" wrote in message ... I tried all 3 suggestions, but they didn't work. "Biff" wrote: Hi! See this: http://tinyurl.com/aml9c Biff "Pe66les" wrote in message ... 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!
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 "Pe66les" wrote in message ... 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? "Biff" wrote: 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 "Pe66les" wrote in message ... It is a date cell. It says Type : date "Biff" wrote: 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 "Pe66les" wrote in message ... I tried all 3 suggestions, but they didn't work. "Biff" wrote: Hi! See this: http://tinyurl.com/aml9c Biff "Pe66les" wrote in message ... 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. |
Biff, it still counts dates prior to F1.
"Biff" wrote: 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 "Pe66les" wrote in message ... 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? "Biff" wrote: 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 "Pe66les" wrote in message ... It is a date cell. It says Type : date "Biff" wrote: 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 "Pe66les" wrote in message ... I tried all 3 suggestions, but they didn't work. "Biff" wrote: Hi! See this: http://tinyurl.com/aml9c Biff "Pe66les" wrote in message ... 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. |
Can you send me a copy of the file? If so, let me know how to contact you.
Biff "Pe66les" wrote in message ... Biff, it still counts dates prior to F1. "Biff" wrote: 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 "Pe66les" wrote in message ... 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? "Biff" wrote: 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 "Pe66les" wrote in message ... It is a date cell. It says Type : date "Biff" wrote: 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 "Pe66les" wrote in message ... I tried all 3 suggestions, but they didn't work. "Biff" wrote: Hi! See this: http://tinyurl.com/aml9c Biff "Pe66les" wrote in message ... 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. |
e-mail - see my profile
"Biff" wrote: Can you send me a copy of the file? If so, let me know how to contact you. Biff "Pe66les" wrote in message ... Biff, it still counts dates prior to F1. "Biff" wrote: 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 "Pe66les" wrote in message ... 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? "Biff" wrote: 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 "Pe66les" wrote in message ... It is a date cell. It says Type : date "Biff" wrote: 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 "Pe66les" wrote in message ... I tried all 3 suggestions, but they didn't work. "Biff" wrote: Hi! See this: http://tinyurl.com/aml9c Biff "Pe66les" wrote in message ... 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. |
[display Can you send me a copy of the file? If so, let me know how to contact you. Biff "Pe66les" wrote in message ... Biff, it still counts dates prior to F1. "Biff" wrote: 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 "Pe66les" wrote in message ... 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? "Biff" wrote: 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 "Pe66les" wrote in message ... It is a date cell. It says Type : date "Biff" wrote: 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 "Pe66les" wrote in message ... I tried all 3 suggestions, but they didn't work. "Biff" wrote: Hi! See this: http://tinyurl.com/aml9c Biff "Pe66les" wrote in message ... 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. |
On Fri, 26 Aug 2005 19:05:04 -0700, "Pe66les"
wrote: 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. 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?
"Ron Rosenfeld" wrote: On Fri, 26 Aug 2005 19:05:04 -0700, "Pe66les" wrote: 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. 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 |
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? "Ron Rosenfeld" wrote: On Fri, 26 Aug 2005 19:05:04 -0700, "Pe66les" wrote: 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. 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 |
On Sat, 27 Aug 2005 01:33:01 -0700, "Pe66les"
wrote: 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 |
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!!! "Pe66les" wrote: 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. |
OK, You get A Double WOOOOOHOOOO!!!!! I corrected my mistake and your
equation is much better than mine. Thank you!!!! Thank you!!! Thank you!!! "Pe66les" wrote: 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!!! "Pe66les" wrote: 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. |
=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 "Ron Rosenfeld" wrote in message ... On Sat, 27 Aug 2005 01:33:01 -0700, "Pe66les" wrote: 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 |
On Sat, 27 Aug 2005 14:48:41 -0400, "Biff" wrote:
=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! 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 |
| All times are GMT +1. The time now is 09:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com