how to count if the value falls between a date range

J

joe

example
1/1/2005
1/2/2005 = 2
2/1/2005
2/2/2005 = 2

I am trying to get the ount if the value is between 1/12005 and 1/3/2005 and
similarly 2/1/2005 and 2/3/2005
 
D

Dave Peterson

=countif(a1:a10,">="&date(2005,1,1)) - countif(a1:a10,">"&date(2005,1,3))

(I'm not sure what between means--include the end dates or not???)
 
M

michaelacumming

Dave,

"Between" is inclusive of the beginning and end dates. This isn't
a very elegant solution, but it will work:

{=SUM(IF(A1:A4>=DATE(2005,1,1),IF(A1:A4<=DATE(2005,1,3),1,0),0))}

Note: This is entered as an array formula, so make sure to hit
CTRL+SHIFT+ENTER after typing the formula in the cell.

HTH

Michael Cumming
 
D

Dave Peterson

It might mean that for you, but I don't think that's a universal interpretation
<bg>.

But you could alter that other formula to include/exclude the end dates.)
 
Top