Announcement

Collapse
No announcement yet.

Count Occurrences Within Date Range

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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
    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.

    Comment


    • #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.

      Comment


      • #4
        Re: Count Number Of Occurences Within A Date Range

        PivotTable

        Comment


        • #5
          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.

          Comment


          • #6
            Re: Count Occurences Within Date Range

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

            Comment


            • #7
              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.

              Code:
              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

              Comment


              • #8
                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

                Comment


                • #9
                  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, 01:10. Reason: delete quote

                  Comment


                  • #10
                    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

                    Comment

                    Working...
                    X