counting the times days are used in a month

[rustybenson]

Hi
I have a list of dates down a work sheet. Some dates appear more than once. I want to count how many times a date appears per month for a 12 month period.
a1; 1/7/2010
a2; 2/7/2010
a3; 10/7/2010
a4; /8/2010
a5; 15/8/2010
a6; 29/9/2010
a7; 30/9/2010
a8; 30/9/2010

Seventh month has 3 dates
eighth month has 2 dates
nineth month has 3 dates.

Can this be done?
Thanks for any help provided.
Regards Rusty

 

[AlphaFrog]

Excel Workbook
	A	B	C	D
1	1/7/2010		Month	Count
2	2/7/2010		1	0
3	10/7/2010		2	0
4	1/8/2010		3	0
5	15/8/2010		4	0
6	29/9/2010		5	0
7	30/9/2010		6	0
8	30/9/2010		7	3
9			8	2
10			9	3
11			10	0
12			11	0
13			12	0
Sheet

 

[Aladin Akyurek]

Hi
I have a list of dates down a work sheet. Some dates appear more than once. I want to count how many times a date appears per month for a 12 month period.
a1; 1/7/2010
a2; 2/7/2010
a3; 10/7/2010
a4; /8/2010
a5; 15/8/2010
a6; 29/9/2010
a7; 30/9/2010
a8; 30/9/2010

Seventh month has 3 dates
eighth month has 2 dates
nineth month has 3 dates.

Can this be done?
Thanks for any help provided.
Regards Rusty

In D1:D12 enter: Jan, Feb,...,Dec

In E1 enter: 2010

In E2 enter and copy down:

=SUMPRODUCT(($A$1:$A$8-DAY($A$1:$A$8)+1=("1-"&E$1&"-"&$D2)+0)+0)

 

[rustybenson]

Hi
Thanks for the replies.
I have tried both answers. The first one works fine however if I have the column formated to date and a zero entry it counts the 1/1/1900 in the month 1 total.
The second reply gives me a "value" error.
Regards
Rusty

 

[AlphaFrog]

Why are you formatting the counts column as Date? The counts are counts-of-dates. Not dates themselves.

 

[Aladin Akyurek]

Hi
Thanks for the replies.
I have tried both answers. The first one works fine however if I have the column formated to date and a zero entry it counts the 1/1/1900 in the month 1 total.
The second reply gives me a "value" error.
Regards
Rusty

What is the result of:

=SUMPRODUCT(ISNUMBER(A$1:$A$8)+0)

 

[rustybenson]

I am posting as dates as it refers to a spread sheet that records incidents that happen. I run the main spread sheet and pull information out to a record sheet that I cn build reports from. So the date process is carried to the record sheet so i can record the amount of incidents in a month to pass on to management.

 

[rustybenson]

What is the result of:

=SUMPRODUCT(ISNUMBER(A$1:$A$8)+0)
This counts the number of dates not the number of dates per month.

 

[Aladin Akyurek]

What is the result of:

=SUMPRODUCT(ISNUMBER(A$1:$A$8)+0)
This counts the number of dates not the number of dates per month.

That's exactly what I want to know...
You claimed that the suggestion I made earlier yielded a #VALUE1 error.
Such an outcome suggests that you have text values in A1:A8, not true dates (numbers). Any comment?

 

[rustybenson]

I have checked . They are in as dates ( Australia Date DD/MM/YYYY)