# Thread: Counting Dates that Occur in a Particular Year (2003)

1. ## Counting Dates that Occur in a Particular Year (2003)

I have a spreadsheet with dates in column E that span from 2004 through 2007.
I need a formula to count the number of dates in the list for each year. (There are over 1500 dates in the list)
I have named the range rptdates and would like to use this in the formula.
I have in another sheet in the same workbook, listed the years across columns -2004,2005,2006,2007-and would like the result/count to be in the cell below each year
Any help/ideas would be greatly appreciated.

2. ## Re: Counting Dates that Occur in a Particular Year

Hi There

You could try:

{=SUMPRODUCT((A1:A100>=J1)*(A1:A100<J2))} where J1 = 1/1/2006 and J2 = 31/12/2006 and press Ctrl +Shift + Enter for an array formula you

3. ## Re: Counting Dates that Occur in a Particular Year

Thanks it worked great, I didn't use CTRL-SHIFT-ENTER though. It still worked thanks again

4. ## Re: Counting Dates that Occur in a Particular Year

An alternative is to create a column next to the date and use the formula =Year(A1). You could then do a pivot table.

1) Highlight new column
2) Select Data | Pivot Table and Pivot Chart Report
3) Next
4) Confirm Range
5) Next
6) Click Layout
7) Drag the year into Column and then again Data (don't worry it will say sum...we will change that later)
8) Click Finish

With the pivot table active (mouse click in) Click Pivot Table Menu and select:

9) Field Settings and select Count
10) Press OK

You now have a column counted list of years..........enjoy

5. ## Re: Counting Dates that Occur in a Particular Year

Jerry,
SUMPRODUCT is designed to work with arrays like that so doesn't need to be array -entered here. FWIW.

6. ## Re: Counting Dates that Occur in a Particular Year

Cheers Rory that is good to know, I suppose it was just habit <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

7. ## Re: Counting Dates that Occur in a Particular Year

I tried the pivot table suggestion -but I didn't get past the Year formula it returned the wrong date. I made sure all the cells in the columns where the original date is and the formula is were Date Formatted-see below: The Year formula returns the results in the column to the right and references the column to the left.
1/5/2004 6/26/05
1/2/2002 6/24/05
1/8/2002 6/24/05
1/9/2002 6/24/05
1/14/2002 6/24/05
1/2/2002 6/24/05
1/4/2002 6/24/05
1/4/2002 6/24/05
1/4/2002 6/24/05
1/5/2002 6/24/05
1/8/2002 6/24/05
1/11/2002 6/24/05
1/12/2002 6/24/05
1/16/2002 6/24/05
1/16/2002 6/24/05
1/21/2002 6/24/05
1/15/2002 6/24/05
1/22/2002 6/24/05
I tried this in a new workbook and the Year formula worked, but in the workbook where I want to use it I get the above results. This workbook could have been created in an older version of Excel. Would that matter?

8. ## Re: Counting Dates that Occur in a Particular Year

Hi There

How strange, I have added this workbook for you to see how I did it.

9. ## Re: Counting Dates that Occur in a Particular Year

Thanks, I'll try this and let you know.

10. ## Re: Counting Dates that Occur in a Particular Year

Hi,
Those results are actually correct but the cells are formatted as dates. Excel treats dates as number of days since 31/12/1899 - so where your year formula returns 2004, if the cell is formatted as a date, Excel counts 2004 days from 31/12/1899 which gives you 26 Jun 1905. If you format the cell as General you will see 2004 etc.
HTH

11. ## Re: Counting Dates that Occur in a Particular Year

That's good info and it was exactly the problem thanks to all. I've been working so I haven' t had a chance to try the Pivot yet but I will and I will let you know how it goes thanks.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•