Counting dates
 

I have a list of dates ranging from 9/1/2007 to 3/31/2008. I'd like
to create a function that would count for each month, is that
possible? Example, count only those dates in November 2007

Counting dates
 

One way...

A1 = 9/1/2007
B1 = 3/31/2008

=SUMPRODUCT(--(TEXT(ROW(INDIRECT(A1&":"&B1)),"mmm/yy")="Nov/07"))

--
Biff
Microsoft Excel MVP


"tonyalt3" wrote in message
...
I have a list of dates ranging from 9/1/2007 to 3/31/2008. I'd like
to create a function that would count for each month, is that
possible? Example, count only those dates in November 2007

Counting dates
 

On Apr 16, 4:39*pm, "T. Valko" wrote:
One way...

A1 = 9/1/2007
B1 = 3/31/2008

=SUMPRODUCT(--(TEXT(ROW(INDIRECT(A1&":"&B1)),"mmm/yy")="Nov/07"))

--
Biff
Microsoft Excel MVP

"tonyalt3" wrote in message

...



I have a list of dates ranging from 9/1/2007 to 3/31/2008. *I'd like
to create a function that would count for each month, is that
possible? *Example, count only those dates in November 2007- Hide quoted text -

- Show quoted text -

What if all dates are in Column A?

Counting dates
 

Try:

=SUMPRODUCT((MONTH(A1:A1000)=11)*(YEAR(A1:A1000)=2 007))

for January you have to also check for empty cells otherwise it will return
false positives:

=SUMPRODUCT((A1:A1000<"")*(MONTH(A1:A1000)=1)*(YE AR(A1:A1000)=2007))

Adjust rabges to suit your requirements but note that you can't use whole
columns.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"tonyalt3" wrote in message
...
I have a list of dates ranging from 9/1/2007 to 3/31/2008. I'd like
to create a function that would count for each month, is that
possible? Example, count only those dates in November 2007

Counting dates
 

Ooops! Sorry, I'm a bit under the weather today and can't seem to think
straight!

I interpreted your post to mean you had just the 2 dates, a start date and
then an end date.

Ok, let's assume your dates are in the range A1:A100.

=SUMPRODUCT(--(TEXT(A1:A100,"mmm/yy")="Nov/07"))


--
Biff
Microsoft Excel MVP


"tonyalt3" wrote in message
...
On Apr 16, 4:39 pm, "T. Valko" wrote:
One way...

A1 = 9/1/2007
B1 = 3/31/2008

=SUMPRODUCT(--(TEXT(ROW(INDIRECT(A1&":"&B1)),"mmm/yy")="Nov/07"))

--
Biff
Microsoft Excel MVP

"tonyalt3" wrote in message

...



I have a list of dates ranging from 9/1/2007 to 3/31/2008. I'd like
to create a function that would count for each month, is that
possible? Example, count only those dates in November 2007- Hide quoted
text -

- Show quoted text -

What if all dates are in Column A?

Counting dates
 

On Wed, 16 Apr 2008 14:15:30 -0700 (PDT), tonyalt3 wrote:

I have a list of dates ranging from 9/1/2007 to 3/31/2008. I'd like
to create a function that would count for each month, is that
possible? Example, count only those dates in November 2007

If your list of dates is in Column A; and some date in the month of November
2007 is in B1, then:

=COUNTIF(A:A,"="&B1-DAY(B1)+1)-
COUNTIF(A:A,""&B1-DAY(B1)+32-DAY(B1-DAY(B1)+32))

--ron