Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Count Occurrences Within Date Range

1. I agreed to these rules
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. ## 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).

3. I agreed to these rules
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. ## Re: Count Number Of Occurences Within A Date Range

PivotTable

5. I agreed to these rules
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. ## Re: Count Occurences Within Date Range

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

7. Have VBA, will travel
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

```

8. Have VBA, will travel
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.

9. I agreed to these rules
Join Date
6th 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.
Last edited by AAE; February 6th, 2011 at 01:10. Reason: delete quote

Excel Video Tutorials / Excel Dashboards Reports

10. ## Re: Count Occurrences Within Date Range

Welcome to Ozgrid.

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

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.

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

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