COUNTIF with Dates

[SRL]

I have two spreadsheets set up. One will be filled individually by employees
and the other will be used by management to track progress.
In the employees spreadsheet I have the following dates:
10/23/2008
10/27/2008
10/29/2008
10/28/2008
11/3/2008

In the management spreadsheet there will be two cells that they will modify
based on the reporting period desired
Start Date: 10/27/08 (this data is in A2)
End Date: 10/31/08 (this data is in B2)
I've been attempting to use COUNTIF to count the instances in which the
date is >start date but <end date. I have tried several combinations to no
avail.
first: =COUNTIF(range,">A2<B2")
I am hoping for a result of 3, but always get back 0.
I also tried: =countif(range,">A2")-countif(range,">B2")
Result is also 0.

Any ideas?

 

[Chase Casey]

I have used a similar function to what you need.
=COUNTIFS(A1:A100,">=9/28/2008",A1:A100,"<=12/27/2008")

Make sure you choose the COUNTIFS with the because you have 2 dates to
include in the function.

 

[RagDyeR]

First of all, according to your explanation, your logic is off.

You say you want count the dates *greater then* the start date,
AND
*less then* the end date.

That leaves only 2 dates in your posted example.

Assuming that you really want to *include* the start and end dates in the
count, try this:

=Countif(range,">="&A2)-Countif(range,">"&B2)

OR

=Sumproduct((range>=A2)*(range<=B2))
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


I have two spreadsheets set up. One will be filled individually by
employees
and the other will be used by management to track progress.
In the employees spreadsheet I have the following dates:
10/23/2008
10/27/2008
10/29/2008
10/28/2008
11/3/2008

In the management spreadsheet there will be two cells that they will modify
based on the reporting period desired
Start Date: 10/27/08 (this data is in A2)
End Date: 10/31/08 (this data is in B2)
I've been attempting to use COUNTIF to count the instances in which the
date is >start date but <end date. I have tried several combinations to no
avail.
first: =COUNTIF(range,">A2<B2")
I am hoping for a result of 3, but always get back 0.
I also tried: =countif(range,">A2")-countif(range,">B2")
Result is also 0.

Any ideas?

 

[ShaneDevenshire]

Hi,

Regarding the COUNTIFS suggestion - that is new to 2007, so don't try it in
2003 or earlier.

I like the SUMPRODUCT solution but it may be a little obscure so as an
alternative you could use

=COUNTIF(Range,">="&B2)-COUNTIF(Range,">"&A2)

where A2 is the end date and B2 is the start date.

If this helps, please click the Yes button.