In a spreadsheet I have dates and times of events (example 10/2/2009 03:12)
in cells F2:S233. I need to count the events by month, ie January had 'x'
number of events, February had 'x' number of events, and so on. For my need,
the time is irrelevant. Your suggestion would be appreciated. Thanks.

Hi

This will count number of events in January in range F2:S233

=COUNTIF(F2:S233;">=" &DATE(2009;1;1))-COUNTIF(F2:S233;">=" &
DATE(2009;2;1))

The second parameter in the DATE function is the month, and has to be
increased by 1 to count events for February.

Hopes this helps.
....
Per

"wally" <(E-Mail Removed)> skrev i meddelelsen
news:7D0108E3-73FB-44A9-82CF-(E-Mail Removed)...
> In a spreadsheet I have dates and times of events (example 10/2/2009
> 03:12)
> in cells F2:S233. I need to count the events by month, ie January had 'x'
> number of events, February had 'x' number of events, and so on. For my
> need,
> the time is irrelevant. Your suggestion would be appreciated. Thanks.

Another one:
=SUMPRODUCT(--(TEXT(F2:S233,"yyyymm")="200901"))

This will calculate slower than Per's suggestion--especially if the range gets
larger, but it's easier to type <vbg>.





wally wrote:
>
> In a spreadsheet I have dates and times of events (example 10/2/2009 03:12)
> in cells F2:S233. I need to count the events by month, ie January had 'x'
> number of events, February had 'x' number of events, and so on. For my need,
> the time is irrelevant. Your suggestion would be appreciated. Thanks.

--

Dave Peterson

Per;
I'm sorry, but I could not make your suggestion work. When pressing ENTER
an error dialog box appears and the first S233 is highlighted. Can you tell
me what I'm doing wrong? I would like to better understand your suggestion.
Thanks.
Wally

"Per Jessen" wrote:

> Hi
>
> This will count number of events in January in range F2:S233
>
> =COUNTIF(F2:S233;">=" &DATE(2009;1;1))-COUNTIF(F2:S233;">=" &
> DATE(2009;2;1))
>
> The second parameter in the DATE function is the month, and has to be
> increased by 1 to count events for February.
>
> Hopes this helps.
> ....
> Per
>
> "wally" <(E-Mail Removed)> skrev i meddelelsen
> news:7D0108E3-73FB-44A9-82CF-(E-Mail Removed)...
> > In a spreadsheet I have dates and times of events (example 10/2/2009
> > 03:12)
> > in cells F2:S233. I need to count the events by month, ie January had 'x'
> > number of events, February had 'x' number of events, and so on. For my
> > need,
> > the time is irrelevant. Your suggestion would be appreciated. Thanks.
>
> .
>

Try replacing all the semi-colons with commas. Some international locations
use the semi-colon as the argument separator and some use the comma.

Even if you get the formula straightened out and Excel will accept it I'm
wondering if Excel will recognize your entries as true Excel dates/times:

>(example 10/2/2009 03:12)

If I enter 10/2/2009 03:12 in a cell Excel treats it as a TEXT string. If I
remove the leading 0 in the time portion *then* Excel treats it as a true
Excel date/time.

--
Biff
Microsoft Excel MVP


"wally" <(E-Mail Removed)> wrote in message
news:50E30CA8-B088-49F8-8015-(E-Mail Removed)...
> Per;
> I'm sorry, but I could not make your suggestion work. When pressing ENTER
> an error dialog box appears and the first S233 is highlighted. Can you
> tell
> me what I'm doing wrong? I would like to better understand your
> suggestion.
> Thanks.
> Wally
>
> "Per Jessen" wrote:
>
>> Hi
>>
>> This will count number of events in January in range F2:S233
>>
>> =COUNTIF(F2:S233;">=" &DATE(2009;1;1))-COUNTIF(F2:S233;">=" &
>> DATE(2009;2;1))
>>
>> The second parameter in the DATE function is the month, and has to be
>> increased by 1 to count events for February.
>>
>> Hopes this helps.
>> ....
>> Per
>>
>> "wally" <(E-Mail Removed)> skrev i meddelelsen
>> news:7D0108E3-73FB-44A9-82CF-(E-Mail Removed)...
>> > In a spreadsheet I have dates and times of events (example 10/2/2009
>> > 03:12)
>> > in cells F2:S233. I need to count the events by month, ie January had
>> > 'x'
>> > number of events, February had 'x' number of events, and so on. For my
>> > need,
>> > the time is irrelevant. Your suggestion would be appreciated. Thanks.
>>
>> .
>>

This works for what I wanted to do. Great job. Thanks.


"Dave Peterson" wrote:

> Another one:
> =SUMPRODUCT(--(TEXT(F2:S233,"yyyymm")="200901"))
>
> This will calculate slower than Per's suggestion--especially if the range gets
> larger, but it's easier to type <vbg>.
>
>
>
>
>
> wally wrote:
> >
> > In a spreadsheet I have dates and times of events (example 10/2/2009 03:12)
> > in cells F2:S233. I need to count the events by month, ie January had 'x'
> > number of events, February had 'x' number of events, and so on. For my need,
> > the time is irrelevant. Your suggestion would be appreciated. Thanks.
>
> --
>
> Dave Peterson
> .
>

Biff: I really appreciate that you tried to work this out. It stilll
doesn't work. I feel you are right that Excel will not recognize the
entries. You might look at the suggestion of Dave Peterson where he is
treating this as a text string. That worked! It did what I was hoping to
achieve. I would still like to understand the construction and use of the
symbols used in the proposal you tried to fix. Can you recommend a resource
that would help? I can't tell you how much I appreciate 'My People'... You
are the best.


"T. Valko" wrote:

> Try replacing all the semi-colons with commas. Some international locations
> use the semi-colon as the argument separator and some use the comma.
>
> Even if you get the formula straightened out and Excel will accept it I'm
> wondering if Excel will recognize your entries as true Excel dates/times:
>
> >(example 10/2/2009 03:12)
>
> If I enter 10/2/2009 03:12 in a cell Excel treats it as a TEXT string. If I
> remove the leading 0 in the time portion *then* Excel treats it as a true
> Excel date/time.
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "wally" <(E-Mail Removed)> wrote in message
> news:50E30CA8-B088-49F8-8015-(E-Mail Removed)...
> > Per;
> > I'm sorry, but I could not make your suggestion work. When pressing ENTER
> > an error dialog box appears and the first S233 is highlighted. Can you
> > tell
> > me what I'm doing wrong? I would like to better understand your
> > suggestion.
> > Thanks.
> > Wally
> >
> > "Per Jessen" wrote:
> >
> >> Hi
> >>
> >> This will count number of events in January in range F2:S233
> >>
> >> =COUNTIF(F2:S233;">=" &DATE(2009;1;1))-COUNTIF(F2:S233;">=" &
> >> DATE(2009;2;1))
> >>
> >> The second parameter in the DATE function is the month, and has to be
> >> increased by 1 to count events for February.
> >>
> >> Hopes this helps.
> >> ....
> >> Per
> >>
> >> "wally" <(E-Mail Removed)> skrev i meddelelsen
> >> news:7D0108E3-73FB-44A9-82CF-(E-Mail Removed)...
> >> > In a spreadsheet I have dates and times of events (example 10/2/2009
> >> > 03:12)
> >> > in cells F2:S233. I need to count the events by month, ie January had
> >> > 'x'
> >> > number of events, February had 'x' number of events, and so on. For my
> >> > need,
> >> > the time is irrelevant. Your suggestion would be appreciated. Thanks.
> >>
> >> .
> >>
>
>
> .
>

=COUNTIF(F2:S233,">="&DATE(2009,1,1))-COUNTIF(F2:S233,">="&
DATE(2009,2,1))

That would count all dates in Jan uary 2009.

The first COUNTIF counts *all* dates that are >= Jan 1 2009.
The second COUNTIF counts *all* dates that are >= Feb 1 2009

The second COUNTIF is subtracted from the first COUNTIF and the result is
the count of dates for Jan 2009.

12/31/2008
1/1/2009
1/15/2009
1/31/2009
2/1/2009
2/10/2009

=COUNTIF(A1:A6,">="&DATE(2009,1,1))-COUNTIF(A1:A6,">="&DATE(2009,2,1))

Format as General

Result = 3

COUNTIF(A1:A6,">="&DATE(2009,1,1)) = 5 (5 dates >= Jan 1 2009)

COUNTIF(A1:A6,">="&DATE(2009,2,1)) = 2 (2 dates >= Feb 1 2009)

5-2 = 3 (3 dates in Jan 2009)

--
Biff
Microsoft Excel MVP


"wally" <(E-Mail Removed)> wrote in message
news:5C766F2D-FB78-4E13-8FB0-(E-Mail Removed)...
> Biff: I really appreciate that you tried to work this out. It stilll
> doesn't work. I feel you are right that Excel will not recognize the
> entries. You might look at the suggestion of Dave Peterson where he is
> treating this as a text string. That worked! It did what I was hoping to
> achieve. I would still like to understand the construction and use of the
> symbols used in the proposal you tried to fix. Can you recommend a
> resource
> that would help? I can't tell you how much I appreciate 'My People'...
> You
> are the best.
>
>
> "T. Valko" wrote:
>
>> Try replacing all the semi-colons with commas. Some international
>> locations
>> use the semi-colon as the argument separator and some use the comma.
>>
>> Even if you get the formula straightened out and Excel will accept it I'm
>> wondering if Excel will recognize your entries as true Excel dates/times:
>>
>> >(example 10/2/2009 03:12)
>>
>> If I enter 10/2/2009 03:12 in a cell Excel treats it as a TEXT string. If
>> I
>> remove the leading 0 in the time portion *then* Excel treats it as a true
>> Excel date/time.
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "wally" <(E-Mail Removed)> wrote in message
>> news:50E30CA8-B088-49F8-8015-(E-Mail Removed)...
>> > Per;
>> > I'm sorry, but I could not make your suggestion work. When pressing
>> > ENTER
>> > an error dialog box appears and the first S233 is highlighted. Can you
>> > tell
>> > me what I'm doing wrong? I would like to better understand your
>> > suggestion.
>> > Thanks.
>> > Wally
>> >
>> > "Per Jessen" wrote:
>> >
>> >> Hi
>> >>
>> >> This will count number of events in January in range F2:S233
>> >>
>> >> =COUNTIF(F2:S233;">=" &DATE(2009;1;1))-COUNTIF(F2:S233;">=" &
>> >> DATE(2009;2;1))
>> >>
>> >> The second parameter in the DATE function is the month, and has to be
>> >> increased by 1 to count events for February.
>> >>
>> >> Hopes this helps.
>> >> ....
>> >> Per
>> >>
>> >> "wally" <(E-Mail Removed)> skrev i meddelelsen
>> >> news:7D0108E3-73FB-44A9-82CF-(E-Mail Removed)...
>> >> > In a spreadsheet I have dates and times of events (example 10/2/2009
>> >> > 03:12)
>> >> > in cells F2:S233. I need to count the events by month, ie January
>> >> > had
>> >> > 'x'
>> >> > number of events, February had 'x' number of events, and so on. For
>> >> > my
>> >> > need,
>> >> > the time is irrelevant. Your suggestion would be appreciated.
>> >> > Thanks.
>> >>
>> >> .
>> >>
>>
>>
>> .
>>

Thanks Biff. I'll work on that. You have been very patient and I appreciate
that.


"T. Valko" wrote:

> =COUNTIF(F2:S233,">="&DATE(2009,1,1))-COUNTIF(F2:S233,">="&
> DATE(2009,2,1))
>
> That would count all dates in Jan uary 2009.
>
> The first COUNTIF counts *all* dates that are >= Jan 1 2009.
> The second COUNTIF counts *all* dates that are >= Feb 1 2009
>
> The second COUNTIF is subtracted from the first COUNTIF and the result is
> the count of dates for Jan 2009.
>
> 12/31/2008
> 1/1/2009
> 1/15/2009
> 1/31/2009
> 2/1/2009
> 2/10/2009
>
> =COUNTIF(A1:A6,">="&DATE(2009,1,1))-COUNTIF(A1:A6,">="&DATE(2009,2,1))
>
> Format as General
>
> Result = 3
>
> COUNTIF(A1:A6,">="&DATE(2009,1,1)) = 5 (5 dates >= Jan 1 2009)
>
> COUNTIF(A1:A6,">="&DATE(2009,2,1)) = 2 (2 dates >= Feb 1 2009)
>
> 5-2 = 3 (3 dates in Jan 2009)
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "wally" <(E-Mail Removed)> wrote in message
> news:5C766F2D-FB78-4E13-8FB0-(E-Mail Removed)...
> > Biff: I really appreciate that you tried to work this out. It stilll
> > doesn't work. I feel you are right that Excel will not recognize the
> > entries. You might look at the suggestion of Dave Peterson where he is
> > treating this as a text string. That worked! It did what I was hoping to
> > achieve. I would still like to understand the construction and use of the
> > symbols used in the proposal you tried to fix. Can you recommend a
> > resource
> > that would help? I can't tell you how much I appreciate 'My People'...
> > You
> > are the best.
> >
> >
> > "T. Valko" wrote:
> >
> >> Try replacing all the semi-colons with commas. Some international
> >> locations
> >> use the semi-colon as the argument separator and some use the comma.
> >>
> >> Even if you get the formula straightened out and Excel will accept it I'm
> >> wondering if Excel will recognize your entries as true Excel dates/times:
> >>
> >> >(example 10/2/2009 03:12)
> >>
> >> If I enter 10/2/2009 03:12 in a cell Excel treats it as a TEXT string. If
> >> I
> >> remove the leading 0 in the time portion *then* Excel treats it as a true
> >> Excel date/time.
> >>
> >> --
> >> Biff
> >> Microsoft Excel MVP
> >>
> >>
> >> "wally" <(E-Mail Removed)> wrote in message
> >> news:50E30CA8-B088-49F8-8015-(E-Mail Removed)...
> >> > Per;
> >> > I'm sorry, but I could not make your suggestion work. When pressing
> >> > ENTER
> >> > an error dialog box appears and the first S233 is highlighted. Can you
> >> > tell
> >> > me what I'm doing wrong? I would like to better understand your
> >> > suggestion.
> >> > Thanks.
> >> > Wally
> >> >
> >> > "Per Jessen" wrote:
> >> >
> >> >> Hi
> >> >>
> >> >> This will count number of events in January in range F2:S233
> >> >>
> >> >> =COUNTIF(F2:S233;">=" &DATE(2009;1;1))-COUNTIF(F2:S233;">=" &
> >> >> DATE(2009;2;1))
> >> >>
> >> >> The second parameter in the DATE function is the month, and has to be
> >> >> increased by 1 to count events for February.
> >> >>
> >> >> Hopes this helps.
> >> >> ....
> >> >> Per
> >> >>
> >> >> "wally" <(E-Mail Removed)> skrev i meddelelsen
> >> >> news:7D0108E3-73FB-44A9-82CF-(E-Mail Removed)...
> >> >> > In a spreadsheet I have dates and times of events (example 10/2/2009
> >> >> > 03:12)
> >> >> > in cells F2:S233. I need to count the events by month, ie January
> >> >> > had
> >> >> > 'x'
> >> >> > number of events, February had 'x' number of events, and so on. For
> >> >> > my
> >> >> > need,
> >> >> > the time is irrelevant. Your suggestion would be appreciated.
> >> >> > Thanks.
> >> >>
> >> >> .
> >> >>
> >>
> >>
> >> .
> >>
>
>
> .
>

You're welcome!

--
Biff
Microsoft Excel MVP


"wally" <(E-Mail Removed)> wrote in message
news:3E5A6549-FA02-40A3-A354-(E-Mail Removed)...
> Thanks Biff. I'll work on that. You have been very patient and I
> appreciate
> that.
>
>
> "T. Valko" wrote:
>
>> =COUNTIF(F2:S233,">="&DATE(2009,1,1))-COUNTIF(F2:S233,">="&
>> DATE(2009,2,1))
>>
>> That would count all dates in Jan uary 2009.
>>
>> The first COUNTIF counts *all* dates that are >= Jan 1 2009.
>> The second COUNTIF counts *all* dates that are >= Feb 1 2009
>>
>> The second COUNTIF is subtracted from the first COUNTIF and the result is
>> the count of dates for Jan 2009.
>>
>> 12/31/2008
>> 1/1/2009
>> 1/15/2009
>> 1/31/2009
>> 2/1/2009
>> 2/10/2009
>>
>> =COUNTIF(A1:A6,">="&DATE(2009,1,1))-COUNTIF(A1:A6,">="&DATE(2009,2,1))
>>
>> Format as General
>>
>> Result = 3
>>
>> COUNTIF(A1:A6,">="&DATE(2009,1,1)) = 5 (5 dates >= Jan 1 2009)
>>
>> COUNTIF(A1:A6,">="&DATE(2009,2,1)) = 2 (2 dates >= Feb 1 2009)
>>
>> 5-2 = 3 (3 dates in Jan 2009)
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "wally" <(E-Mail Removed)> wrote in message
>> news:5C766F2D-FB78-4E13-8FB0-(E-Mail Removed)...
>> > Biff: I really appreciate that you tried to work this out. It stilll
>> > doesn't work. I feel you are right that Excel will not recognize the
>> > entries. You might look at the suggestion of Dave Peterson where he is
>> > treating this as a text string. That worked! It did what I was hoping
>> > to
>> > achieve. I would still like to understand the construction and use of
>> > the
>> > symbols used in the proposal you tried to fix. Can you recommend a
>> > resource
>> > that would help? I can't tell you how much I appreciate 'My People'...
>> > You
>> > are the best.
>> >
>> >
>> > "T. Valko" wrote:
>> >
>> >> Try replacing all the semi-colons with commas. Some international
>> >> locations
>> >> use the semi-colon as the argument separator and some use the comma.
>> >>
>> >> Even if you get the formula straightened out and Excel will accept it
>> >> I'm
>> >> wondering if Excel will recognize your entries as true Excel
>> >> dates/times:
>> >>
>> >> >(example 10/2/2009 03:12)
>> >>
>> >> If I enter 10/2/2009 03:12 in a cell Excel treats it as a TEXT string.
>> >> If
>> >> I
>> >> remove the leading 0 in the time portion *then* Excel treats it as a
>> >> true
>> >> Excel date/time.
>> >>
>> >> --
>> >> Biff
>> >> Microsoft Excel MVP
>> >>
>> >>
>> >> "wally" <(E-Mail Removed)> wrote in message
>> >> news:50E30CA8-B088-49F8-8015-(E-Mail Removed)...
>> >> > Per;
>> >> > I'm sorry, but I could not make your suggestion work. When pressing
>> >> > ENTER
>> >> > an error dialog box appears and the first S233 is highlighted. Can
>> >> > you
>> >> > tell
>> >> > me what I'm doing wrong? I would like to better understand your
>> >> > suggestion.
>> >> > Thanks.
>> >> > Wally
>> >> >
>> >> > "Per Jessen" wrote:
>> >> >
>> >> >> Hi
>> >> >>
>> >> >> This will count number of events in January in range F2:S233
>> >> >>
>> >> >> =COUNTIF(F2:S233;">=" &DATE(2009;1;1))-COUNTIF(F2:S233;">=" &
>> >> >> DATE(2009;2;1))
>> >> >>
>> >> >> The second parameter in the DATE function is the month, and has to
>> >> >> be
>> >> >> increased by 1 to count events for February.
>> >> >>
>> >> >> Hopes this helps.
>> >> >> ....
>> >> >> Per
>> >> >>
>> >> >> "wally" <(E-Mail Removed)> skrev i meddelelsen
>> >> >> news:7D0108E3-73FB-44A9-82CF-(E-Mail Removed)...
>> >> >> > In a spreadsheet I have dates and times of events (example
>> >> >> > 10/2/2009
>> >> >> > 03:12)
>> >> >> > in cells F2:S233. I need to count the events by month, ie
>> >> >> > January
>> >> >> > had
>> >> >> > 'x'
>> >> >> > number of events, February had 'x' number of events, and so on.
>> >> >> > For
>> >> >> > my
>> >> >> > need,
>> >> >> > the time is irrelevant. Your suggestion would be appreciated.
>> >> >> > Thanks.
>> >> >>
>> >> >> .
>> >> >>
>> >>
>> >>
>> >> .
>> >>
>>
>>
>> .
>>