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.