Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: Count Occurrences Within Date Range

  1. #1
    Join Date
    16th July 2007
    Posts
    3

    Count Occurrences Within Date Range

    I need to count the number of occurrences of a range of dates in one column. These are not unique dates, but dates between two date periods i.e. number of dates falling between 02/07/07 and 09/07/07.

    I have tried using the COUNTIFS function and inserting the column range (e.g. B7-B57) and then searching for dates <=02/07/07. However, what I need is to search for the number of times any date in a given period occurs i.e. number of 03/07/07s in a period from 02/07/07 to 09/07/07.

    Many thanks

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035

    Re: Count Number Of Occurences Within A Date Range

    I am a bit confused as to what you are after. If you simply want to count the total number of dates in a list that fall between two dates (inclusive) then you could use (for a list of dates in cells B1:B60)

    Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas
     Z
    100 =SUMPRODUCT(($B$1:$B$60>=$D$1)*($B$1:$B$60<=$D$2)) 


    Where cells D1 and D2 contain your starting and ending dates.

    If you want to find the occurrences of specific dates, then use

    Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas
     Z
    100 =SUMPRODUCT(($B$1:$B$60=F1)*1) 


    to find the numebr of tiems the date in cell F1 occurs in the list. (See column G in the attached).
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Best Regards,
    Tom
    ---------------------------
    Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

  3. #3
    Join Date
    16th July 2007
    Posts
    3

    Re: Count Number Of Occurences Within A Date Range

    Thanks Tom, but I realise that I was not clear enough.

    The date range that I need to search does not have a start date in one cell and an end date in another. It is a column featuring dates upon which goods were received. These are entirely random. What I want to do is use the dates as a method of counting the goods received in a certain time period i.e. Between 02/07 and 09/07 if 03/07 appears 3 times and 05/07 appears once, the figure I require at the end of the calculation is 4.

    I believe this is why I need to use the COUNTIFS function.
    =COUNTIFS(V7:V249,">=09/07/07")
    My problem is that I need the above date to be a range not just open-ended.

    Regards.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

  5. #5
    Join Date
    16th July 2007
    Posts
    3

    Re: Count Occurences Within Date Range

    Thanks Dave, but I can't get this to work.

    I copied the script into Visual Basic, but when I try and run the Macro it tells me that there is a run-time error and that I need to select more than one data range for this action.

    The advanced filter solution will not work either as I could not get it to count dates.

    Am pretty stuck now!

    Regards.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

    Re: Count Occurences Within Date Range

    What script? Why not use a PivotTable and Group by dates?

  7. #7
    Join Date
    24th December 2004
    Location
    Nanaimo, Vancouver Island, British Columbia, Canada
    Posts
    2,464

    Re: Count Number Of Occurences Within A Date Range

    Here's a trick.

    The first Countif counts the number of dates in the given range greater than the 'start date'. The second Countif subtracts any date greater than the 'end date'.

    =COUNTIF(A1:A9,">1/1/2007")-COUNTIF(A1:A9,">8/1/2007")

    "I need is to search for the number of times any date in a given period occurs i.e. number of 03/07/07s in a period"

    where "period" becomes a range (A1:A9) is:

    =COUNTIF(A1:A9,">2/1/2007")-COUNTIF(A1:A9,">3/1/2007")

    To use the UDF below, enter

    =DCnt(

    in a cell. Then select the range (or type it in) then a comma ie:

    =DCnt(A1:A9,

    Now either select a cell that has the start date or enter a date in quotes and a comma ie:

    either =DCnt(A1:A9,E2,

    or =DCnt(A1:A9,"1/1/2007",

    Then finish up with another range or quoted date and a final parentheses:

    either =DCnt(A1:A9,E2,E3)

    or =DCnt(A1:A9,"1/1/2007","1/7/2007")

    Press enter.

    VB:
     
    Option Explicit 
     
    Function DCnt(rng As Range, sdate As Date, edate As Date) 
         
        Dim Cel As Range 
        Dim Countr As Long 
         
        For Each Cel In rng 
            If Cel > sdate And Cel < edate Then 
                Countr = Countr + 1 
            End If 
        Next Cel 
         
        DCnt = Countr 
         
    End Function 
    
    
    Cheers,

    dr

    "Questions, help and advice for free, small projects by donation. large projects by quote"

    http://www.ExcelVBA.joellerabu.com

  8. #8
    Join Date
    24th December 2004
    Location
    Nanaimo, Vancouver Island, British Columbia, Canada
    Posts
    2,464

    Re: Count Occurences Within Date Range

    Hi again,

    Just tried Thomachs' formulas. This one does what I think you want as well.

    Presuming your dates are in A1:A9. Put a start date in C1 and an enddate in C2, then enter this formula in the cell where you want the answer to be.

    =SUMPRODUCT((A1:A9>=C1)*(A1:A9<=C2))

    Slick.
    Cheers,

    dr

    "Questions, help and advice for free, small projects by donation. large projects by quote"

    http://www.ExcelVBA.joellerabu.com

  9. #9
    Join Date
    5th February 2011
    Posts
    1

    Re: Count Number Of Occurences Within A Date Range

    This worked straight off the bat for me. I had to say thanks Thomach.
    Adam.
    Last edited by AAE; February 6th, 2011 at 00:10. Reason: delete quote

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    Re: Count Occurrences Within Date Range

    Adam,

    Welcome to Ozgrid.

    Glad you found the solution you need, however, you've posted in a thread that is nearly four years old.

    Please do not quote entire posts. When quoting follow these guidelines

    1. Quote ONLY if it is needed to add clarity or context for your reply. If so, then
    2. Quote ONLY the specific part of the post that is relevant - - not the entire post.

    This will keep thread clutter to a minimum and make the discussion easier to follow.
    Thanks.
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. How to count the occurrences of a number or text in a range in Excel ...
    By Dave Hawley in forum Excel Formulas (No Questions)
    Replies: 0
    Last Post: April 11th, 2008, 14:05
  2. Count Most Occurrences Of Value In Range
    By iain_benson in forum EXCEL HELP
    Replies: 5
    Last Post: April 11th, 2008, 00:21
  3. Count The Number Of Occurrences Of A Number In A Range
    By wannlearnvba in forum EXCEL HELP
    Replies: 2
    Last Post: May 5th, 2007, 00:45
  4. Count occurrences of letters
    By Alexk092 in forum EXCEL HELP
    Replies: 2
    Last Post: December 8th, 2004, 17:30

Bookmarks

Posting Permissions

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