Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    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

    adjust range to fit
    Jerry

  3. #3
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    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
    Jerry

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    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>
    Jerry

  7. #7
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #8
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    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.
    Jerry

  9. #9
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting Dates that Occur in a Particular Year

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

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    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
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •