count the occurrences of a month in a date&time cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tom - 13 Feb 2008 16:09 GMT

Hi,

I'm trying to count the occurrences of any given month in a rage where the
cells are in a date&time format. (11/02/2008 11:22:53)

For example in the range A1:A30 I want to count the number of times the
month of February occurs.

I've tried a count(if(mid formula but my brain melted after the 10 attempt.

Any help would be greatly appreciated.

Thanks,

Reply to this Message

Max - 13 Feb 2008 16:27 GMT

> For example in the range A1:A30 I want to count the number of times the
> month of February occurs.

Try: =SUMPRODUCT((MONTH(A1:A30)=2)*(A1:A30<>""))

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> I'm trying to count the occurrences of any given month in a rage where the
> cells are in a date&time format. (11/02/2008 11:22:53)
[quoted text clipped - 7 lines]
>
> Thanks,

Reply to this Message

David Biddulph - 13 Feb 2008 18:13 GMT

Do you need the <>"" test? Would you satisfy the MONTH=2 test if column A
had =""?

Signature

David Biddulph

>> For example in the range A1:A30 I want to count the number of times the
>> month of February occurs.
[quoted text clipped - 13 lines]
>>
>> Thanks,

Reply to this Message

Roger Govier - 13 Feb 2008 18:31 GMT

Hi David
I would think Max was generalising, in case the OP tried it for January as
well, as all blank cells would then be counted (00 Jan 1900)
I tried to eliminate that with my posting by including the year (I agree 08
would pick up 1908, but didn't think that likely for the OP).
Of course it would have been better if I had used
text(date,"yyyymmm")="2008Feb"

Signature

Regards
Roger Govier

> Do you need the <>"" test? Would you satisfy the MONTH=2 test if column A
> had =""?
[quoted text clipped - 15 lines]
>>>
>>> Thanks,

Reply to this Message

Max - 13 Feb 2008 23:03 GMT

Yes, I was generalizing the expression for the OP, as per Roger's line:

> I would think Max was generalising, in case the OP tried it for January as
> well, as all blank cells would then be counted (00 Jan 1900)

But perhaps I should have explained it in the response itself for
completeness. Roger, thanks for that favour!

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

Roger Govier - 13 Feb 2008 17:37 GMT

Hi Tom
One way
=SUMPRODUCT(--(TEXT(A1:A30,"yymmm")="08Feb"))

Signature

Regards
Roger Govier

> Hi,
>
[quoted text clipped - 10 lines]
>
> Thanks,

Reply to this Message

Tom - 14 Feb 2008 17:05 GMT

Hi,

Thank you all.

Both solutions worked without a hitch but I'll be going with Roger's as it
gives the option of including a year as well as a moth.

Thank you,
Tom

> Hi Tom
> One way
[quoted text clipped - 14 lines]
> >
> > Thanks,

Reply to this Message