counting date entries by month & year

I have a column with over 11(K) date entries. This column grows daily. I
need a formula that will total the number of entries by month & year
(disregarding the day).

A very simple way to accomplish this is to put in a "helper column"
consisting of:
= text( year( your_date_cell), "####") & " - " & text( month(
your_date_cell), "##")

Then just let Excel subtotal (Data / Subtotal) "Count" on changes in
the helper column. This assumes that the entries are arranged
chronologically in the column containing "your_date_cell".

Chris

Create a helper column and add the year there
=YEAR(A2)
and another with the month
=MONTH(A2)

and then create a pivot table for all of it. Probably simpler than trying to
build yourself.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Di" <[email protected]> wrote in message
news:[email protected]...
> I have a column with over 11(K) date entries. This column grows daily. I
> need a formula that will total the number of entries by month & year
> (disregarding the day).

On Mon, 22 Aug 2005 08:36:52 -0700, "Di" <[email protected]> wrote:

>I have a column with over 11(K) date entries. This column grows daily. I
>need a formula that will total the number of entries by month & year
>(disregarding the day).

Column A = your list of date entries.

B1:Bn A list of dates being the first of each month of interest.

B1: 1 Jan 2000
B2: 1 Feb 2000
B3: 1 Mar 2000
etc.

If you put the above dates in B1 and B2; select B1&B2; place the cursor in the
lower right corner of the selection until it changes to a cross-hair, then
press the left mouse button and drag it down, it will increment automatically.

Copy down further into the future than you need.

Format the cells: Format/Cells/Number/Custom Type: mmm yyyy

C1: =COUNTIF(A:A,">="&B1)-COUNTIF(A:A,">="&B2)

Format as number.
Copy/Drag down as needed.




--ron

What is a helper column? The date column is not arranged chronologically &
is about 50 percent blank.
=MONTH(A2)
=YEAR(A2)
Just gives me the year & month entered in the respective cells but doesn’t
count anything. Could I have the cells formatted incorrectly?



"Bob Phillips" wrote:

> Create a helper column and add the year there
> =YEAR(A2)
> and another with the month
> =MONTH(A2)
>
> and then create a pivot table for all of it. Probably simpler than trying to
> build yourself.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Di" <[email protected]> wrote in message
> news:[email protected]...
> > I have a column with over 11(K) date entries. This column grows daily. I
> > need a formula that will total the number of entries by month & year
> > (disregarding the day).
>
>
>

The date column is not arranged chronologically & is about 50 percent blank.

"Blue Hornet" wrote:

> A very simple way to accomplish this is to put in a "helper column"
> consisting of:
> = text( year( your_date_cell), "####") & " - " & text( month(
> your_date_cell), "##")
>
> Then just let Excel subtotal (Data / Subtotal) "Count" on changes in
> the helper column. This assumes that the entries are arranged
> chronologically in the column containing "your_date_cell".
>
> Chris
>
>

A variation of this worked!! Thanks for the help!

"Ron Rosenfeld" wrote:

> On Mon, 22 Aug 2005 08:36:52 -0700, "Di" <[email protected]> wrote:
>
> >I have a column with over 11(K) date entries. This column grows daily. I
> >need a formula that will total the number of entries by month & year
> >(disregarding the day).
>
> Column A = your list of date entries.
>
> B1:Bn A list of dates being the first of each month of interest.
>
> B1: 1 Jan 2000
> B2: 1 Feb 2000
> B3: 1 Mar 2000
> etc.
>
> If you put the above dates in B1 and B2; select B1&B2; place the cursor in the
> lower right corner of the selection until it changes to a cross-hair, then
> press the left mouse button and drag it down, it will increment automatically.
>
> Copy down further into the future than you need.
>
> Format the cells: Format/Cells/Number/Custom Type: mmm yyyy
>
> C1: =COUNTIF(A:A,">="&B1)-COUNTIF(A:A,">="&B2)
>
> Format as number.
> Copy/Drag down as needed.
>
>
>
>
> --ron
>

On Wed, 24 Aug 2005 07:49:33 -0700, "Di" <[email protected]> wrote:

>A variation of this worked!! Thanks for the help!

Glad to help. Thank you for the feedback.


--ron