Counting Dates

[mike&jess]

Hello Fellow Excellers!

We are trying to create a table with the number of occurances of a date.
We have a VERY long list of project dates (approx 3000), all in date order for each project, not necesarily in numerical order, and a lot of them repeated.
We would like to filter out what dates are in the list, and the occurance of each date; to then be tallied.
For Example;

[TABLE="width: 87"]
<tbody>[TR]
[TD]04/02/2011[/TD]
[/TR]
[TR]
[TD]04/02/2011[/TD]
[/TR]
[TR]
[TD]04/02/2011[/TD]
[/TR]
[TR]
[TD]08/03/2011[/TD]
[/TR]
[TR]
[TD]14/03/2011[/TD]
[/TR]
[TR]
[TD]14/03/2011[/TD]
[/TR]
[TR]
[TD]14/03/2011[/TD]
[/TR]
[TR]
[TD]14/03/2011[/TD]
[/TR]
[TR]
[TD]14/03/2011[/TD]
[/TR]
[TR]
[TD]17/03/2011[/TD]
[/TR]
[TR]
[TD]17/03/2011[/TD]
[/TR]
[TR]
[TD]17/03/2011[/TD]
[/TR]
[TR]
[TD]17/03/2011[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

As there are so many dates, we do not know how many of each are occuring ect, we just have the dates themselves! We don't want to use VBA either please!

Thanks Peeps

 

[DauntingGecko]

Copy all the dates and find the unique list first:

Excel 2007 & 2010: Data tab > remove duplicates
Excel 2003: Data > Filter > Advanced Filter

Then once you have your unique list use a COUNTIF formula.

E.G.

=COUNTIF(RangeOfOriginalDates,UniqueDate)
=COUNTIF(A1:A1000,C1)

This will count how many times it finds the date in cell C1 from your original list (A1:A1000) for example.

 

[Momentman]

You can use a pivot Table. It will give you results like:

[TABLE="width: 185"]
<tbody>[TR]
[TD]Row Labels[/TD]
[TD]Count of Date[/TD]
[/TR]
[TR]
[TD]14/03/2011[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]17/03/2011[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]4/2/2011[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]8/3/2011[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]13[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]