Counting dates

[tonyalt3]

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

 

[T. Valko]

One way...

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

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

 

[tonyalt3]

One way...

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

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

--
Biff
Microsoft Excel MVP






- Show quoted text -

What if all dates are in Column A?

 

[Sandy Mann]

Try:

=SUMPRODUCT((MONTH(A1:A1000)=11)*(YEAR(A1:A1000)=2007))

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

=SUMPRODUCT((A1:A1000<>"")*(MONTH(A1:A1000)=1)*(YEAR(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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[T. Valko]

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

One way...

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

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

--
Biff
Microsoft Excel MVP






- Show quoted text -

What if all dates are in Column A?

 

[Ron Rosenfeld]

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