Count Number of Dates in Date Range

[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

 

[Frank Kabel]

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

 

[Harlan Grove]

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

....

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.

 

[Norman Harker]

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.

 

[Frank Kabel]

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")

 

[Norman Harker]

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.

 

[Frank Kabel]

Hi Norman
also rembered distantly <vbg>

Frank
P.S.: again working late, aren't you

 

[Norman Harker]

Hi Frank!

Late? Only 10:30 PM

You might find the following very good for times and dates elsewhere:

http://www.timeanddate.com/worldclock/fixedtime.html

 

[xtreme]

Hi Frank,

I have tried to get the the index working and could. Could you pleas
download the excel file from http://members.optusnet.com.au/~liamo81
and email the excel file after you have added the formula t
(e-mail address removed)

Thanks heaps,

Liam O