Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Empty Dates within a Range? | Excel Worksheet Functions | |||
Counting number of observations within a date range? | Excel Worksheet Functions | |||
Counting dates within a specified range | Excel Discussion (Misc queries) | |||
Counting dates in a RANGE (yargh!) :) | Excel Worksheet Functions | |||
Counting Dates in a Range | Excel Discussion (Misc queries) |