how to calculate occurence of month-year

[nqueen]

Hi ,
I have a list of dates :
e.g.
31/12/2003
31/12/2004
01/04/2006
24/04/2006
11/05/2008
21/05/2008

I need to count the occurence of each date (just for months/year)
the result should give me:

Project date Results
1 12/2003 1
2 12/2004 1
3 04/2006 2
4 05/2008 2

I tried to take the month of the date(MONTH(date), then the year (YEAR(date)) then I concatenated both (month and year) and I tried to apply the frequency function but it did not work.

Is there a simple way to do it?
My goal is to ultimatly have a graph that shows the number of projects(Results) per month.

thanks in advance!

 

[SkipVought]

Hi,

Use the PivotTable Wizard to get COUNT of DateField.

Righ clkick DateField in PivotTable result and select Group and Outline>Group -- and select Month & Year.

VOLA!

Should take about 15 seconds.



Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[ThomasLafferty]

Hi there! The frequency function won't work for your because it ignores blanks and text. When you concatenate, the values are converted to text. What I would recommend is the COUNTIF function.

Let's say your project number is in the A column, your dates to test are in the B column. Place your concatenated results in the C column with a formula like this:
=MONTH(A2)&"/"&YEAR(A2)

Next, build 2 tables of values: one for charting results, and one for criteria for your COUNTIF formula.

My formula table for results looks like this, from D1:L15

Month	2003	2004	2005	2006	2007	2008	2009	2010
1	
2	
3
4
5
6
7
8
9
10
11
12

Next, I built an identical table of lookup values for criteria, from D15:L27.

Month	2003	2004	2005	2006	2007	2008	2009	2010
1	
2	
3
4
5
6
7
8
9
10
11
12

I next built formulas to fill with Month/Year concatenated values:
=$D16&"/"&E$15

Put the formula in E16, then Autofill over then down to fill your table.

Next, I put a COUNTIF function in cell E2 of my results table. My concatenated project dates are in cells C2:C7, so I refer to them by absolute reference:
=COUNTIF($C$2:$C$7,E16)

Fill this formula over and down to fill your results table, and Voila!

Tom

 

[nqueen]

Thanks guys for you answers.
While I was waiting for your answers, I thought about PivotTables which I worked with but I have both month and year as rows. It would be nice if I could group it as Skip mentionned.
Skip I am trying to figure out how you do your counts of datefield and grouping. Could you show me the steps to take to ?achieve this please?

Tom I ll try your way if this one doesn t work.
Thanks!

 

[SkipVought]

Right click your DateField in PivotTable result and select Group and Outline>Group -- and select Month & Year.

Have you added a pivot table yet?



Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[ThomasLafferty]

Incidentally, I noticed that you have your dates formatted as 31/12/2003 rather than 12/31/2003. Hope this doesn't cause a problem...
Tom

 

[SkipVought]

Tom,

You need a better understanding of Date/Time in Excel.

Why do Dates and Times seem to be so much trouble? faq68-5827

It is almost NEVER a good idea to change any date to string when doing date comparisons. Tables, such as the one you earlier posted, can and ought to be done with full dates, FORMATTED to display as desired.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[ThomasLafferty]

Thanks! I have fallen into some bad habits due to 1)ignorance and 2) laziness. I appreciate the headsup and the link to the FAQ.
Tom

Live once die twice; live twice die once.