counting the number of dates in a date range

[smcmoran]

I have a list of over 5000 birth dates i.e: 2/7/1975. I need to count the
number of times a cell has a date in a date range of years. For example I
need to know how many were born in the years 1927 thruogh 1937.

Scott

 

[Don Guillett]

try
=sumproduct((year(a2:a5000)>=1927)*(year(a2:a5000)<=1937))

 

[John C]

Well, with a little flexibility of counting for more than just years, you
could do the following:
=SUMPRODUCT(($A$2:$A$5001>=firstdate)*($A$2:$A$5001<=lastdate))

Hope this helps.

 

[smcmoran]

Thank you, That worked perfectly and very simply. I was trying to complicate
the formula too much.

 

[Don Guillett]

Glad to help

 

[Axess08]

What if you wanted to look at specific quarter (i.e. a range of months within
a year)? I tried using
=SUMPRODUCT(((C2:C73)>=01012008)*(MONTH(C2:C73)<=12312008))

with and without "month" and I got a zero, so clearly, I am not sure of the
format for the dates.

 

[T. Valko]

To count dates that are in the a specific range...

Use cells to hold the date boundaries:

A1 = start date
B1 = end date

=SUMPRODUCT(--(C2:C73>=A1),--(C2:C73<=B1))

 

[Axess08]

Good Try, but it doesn't quite work. I keep either getting 0 or 72 (which is
the total number of cells). That's ok though. I think this is done better
with Access queries anyway. Thanks for your help!

 

[Spiky]

Your problem is likely still the date format. Excel puts dates in as
an integer, counting days from 1/1/1900. Today, 11/4/2008, is 39756.
Then you can use a Date format to show it as a date.

I'm guessing your reference cells are values with date format, but the
other data, presumably downloaded from Access, is text. That won't
match. Either change the data to values or change the two reference
cells to text.

 

[Ashish Mathur]

Hi,

MONTH(C2:C73)<=12312008

After the equal to sign, shouldn't there be a number (from 1-12) indicating
the month.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com