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

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,

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

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<""))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Tom" wrote:
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,

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

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

--
Regards
Roger Govier

"Tom" wrote in message
...
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,

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

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

"Max" wrote in message
...
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<""))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Tom" wrote:
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,

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

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"
--
Regards
Roger Govier

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Do you need the <"" test? Would you satisfy the MONTH=2 test if column A
had =""?
--
David Biddulph

"Max" wrote in message
...
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<""))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Tom" wrote:
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,

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

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!
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

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

"Roger Govier" wrote:

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

--
Regards
Roger Govier

"Tom" wrote in message
...
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,