counting the number of dates in a date range
 

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

counting the number of dates in a date range
 

try
=sumproduct((year(a2:a5000)=1927)*(year(a2:a5000) <=1937))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"smcmoran" wrote in message
...
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

counting the number of dates in a date range
 

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$500 1<=lastdate))

Hope this helps.
--
John C


"smcmoran" wrote:

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

counting the number of dates in a date range
 

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

"Don Guillett" wrote:

try
=sumproduct((year(a2:a5000)=1927)*(year(a2:a5000) <=1937))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"smcmoran" wrote in message
...
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

counting the number of dates in a date range
 

Glad to help

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"smcmoran" wrote in message
...
Thank you, That worked perfectly and very simply. I was trying to
complicate
the formula too much.

"Don Guillett" wrote:

try
=sumproduct((year(a2:a5000)=1927)*(year(a2:a5000) <=1937))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"smcmoran" wrote in message
...
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

counting the number of dates in a date range
 

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)<=1 2312008))

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


"Don Guillett" wrote:

try
=sumproduct((year(a2:a5000)=1927)*(year(a2:a5000) <=1937))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"smcmoran" wrote in message
...
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

counting the number of dates in a date range
 

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))

--
Biff
Microsoft Excel MVP


"Axess08" wrote in message
...
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)<=1 2312008))

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


"Don Guillett" wrote:

try
=sumproduct((year(a2:a5000)=1927)*(year(a2:a5000) <=1937))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"smcmoran" wrote in message
...
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

counting the number of dates in a date range
 

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!

"T. Valko" wrote:

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))

--
Biff
Microsoft Excel MVP


"Axess08" wrote in message
...
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)<=1 2312008))

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


"Don Guillett" wrote:

try
=sumproduct((year(a2:a5000)=1927)*(year(a2:a5000) <=1937))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"smcmoran" wrote in message
...
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

counting the number of dates in a date range
 

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.

counting the number of dates in a date range
 

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

"Axess08" wrote in message
...
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)<=1 2312008))

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


"Don Guillett" wrote:

try
=sumproduct((year(a2:a5000)=1927)*(year(a2:a5000) <=1937))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"smcmoran" wrote in message
...
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