Count Number of Dates in Date Range

  • Thread starter Thread starter xtreme
  • Start date Start date
X

xtreme

Hi all,

I have a spread sheet that calculates a number of dates.

I it want to count have many dates are within a certain month and i
possible list them in a seperate column.

I can easily highlight them using conditional formating, but then
have to go through an count them manually.

I have some examples at http://members.optusnet.com.au/~liamo81/

Pic 1 Shows an example with the condtional format. It shows that ther
are 6 dates in May 04. It is easy to count manually when I only have
small spread, but when it grows to having 200 columns and 200 rows, i
makes it allot more difficult.

I will also put a copy of this file on the above link also.

If you can help me in any way it would be greatly appreciated.

Cheers

Xtrem
 
Hi
one way to count them:
=SUMPRODUCT(--(MONTH(A1:A100)=5),--(YEAR(A1:A100)=2004))

for listing them in a separate column you could use 'Data - Filter -
Advanced Filter). For a formula solution you may try the following
array formula (entered with CTRL+SHIFT+ENTER):

=INDEX($A$1:$A$100,SMALL(IF(($A$1:$A$100>=DATE(2004,5,1))*($A$1:$A$100<
=DATE(2004,5,31)),ROW($A$1:$A$100)),ROW(1:1)))
and copy this down
 
Frank Kabel said:
one way to count them:
=SUMPRODUCT(--(MONTH(A1:A100)=5),--(YEAR(A1:A100)=2004))
Click to expand...
....

If you're going to hard-code dates in some manner, an alternative is

=COUNTIF(A1:A100,">=7/1/2004")-COUNTIF(A1:A100,">8/1/2004")

Longer, but I'm pretty sure it'll recalc faster.
 
Hi Harlan!

Faster but less robust. With Australian date settings he'll get
different results than with US date settings.

Better, if hard coding to use:

=COUNTIF(A1:A100,">=2004-07-01")-COUNTIF(A1:A100,">2004-08-01")

AFAIK this will work with all language versions of Excel with any
regional settings.
 
Hi
definetly faster and Norman's versions also works for
German settings :-)

In addition Harlan also probably meant
=COUNTIF(A1:A100,">=2004-07-01")-COUNTIF(A1:A100,">=2004-
08-01")
 
Thanks Frank!

I was just looking at the hard coding of dates problem. We discussed
the advisability of using that ISO approved approach earlier this
year.
 
Back
Top