How to Count Date Occurrences in Excel (3 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

This article will focus on how to count date occurrences in Excel from different datasets and different conditions.


Excel Count Date Occurrences: 2 Suitable Examples

We are categorizing all the examples in different sections and subsections. That way, it will be easier to understand. In the first, section, we have demonstrated how you can count particular date occurrences in Excel. The second one includes counting occurrences in a particular range. And finally, we have shown how we can count unique date occurrences in Excel.


1. Number of Occurrences of a Particular Date

For this first case, we will focus on the following dataset.

The dataset consists of a list of works by H. Ridder Haggard and their publication dates. In the following subsections, we will see how we can count the number of occurrences on a particular date using Microsoft Excel’s different functions.


1.1 Using COUNTIF Function

This is the simplest way to count occurrences on a particular date in Excel. The main idea is to use the COUNTIF function to do the work for us. Moreover, we can do it for any type of value, not just dates.

However, the COUNTIF function takes two arguments – one range of cells called range, and particular criteria called criteria. Then it returns the number of cells within that range which maintains the particular criteria.

For the dataset, the steps would be as follows.

Steps:

  • First, select cell E5.
  • Then write down the following formula in it.

=COUNTIF(C5:C243,"18-Aug-1888")

  • After that, press Enter.

excel count date occurrences using countif function

This is how we can easily count occurrences of a particular date in Excel using the COUNTIF function.


1.2 Using SUMPRODUCT Function

Another way we can achieve the same result is to utilize the SUMPRODUCT function. In particular, this function takes one or more range of cells as arguments. It consequently returns their mathematical sum as output. Hence we can use the function to configure a formula to count date occurrences in Excel.

Follow these steps to see how we can use it for this dataset.

Steps:

  • First, select cell E5.
  • Then write down the following formula in int.

=SUMPRODUCT(--(C5:C25="18-Aug-1888"))

🔎 Breakdown of the Formula

👉 Here C4:C23=”18-Aug-1888″ compares every cell in the range C4 to C23 and returns a TRUE if the date is Aus 18, 1888. Otherwise returns FALSE.

👉 The (–) portion converts the array of Boolean values (TRUE and FALSE) into an array of 1 and 0, 1 for TRUE, and 0 for FALSE.

👉 The SUMPRODUCT() function then returns the sum of these 1’s and 0’s. This is the total number of cells that has the date August 18, 1888.

  • Finally, press Enter on your keyboard.

excel count date occurrences using sumproduct funtion

Owing to the formula, this function will now return the total date occurrence of that particular input from the Excel spreadsheet.


1.3 Using Pivot Table

If you want to count the number of occurrences of every date together, then you can use Excel’s Pivot Table feature. This is a powerful tool in Excel, that we can use to calculate, summarize and analyze data. And by summarizing, we can count the total date occurrences for every date in a dataset.

To use this method for the dataset or similar dataset, you need to follow these steps.

Steps:

  • First, select any cell from the dataset.
  • Then go to the Insert tab on your ribbon.
  • Next, select PivotTable from the Tables group section.

  • Additionally, you can make sure of the dataset range and where you want to put the pivot table in the next box. To demonstrate, we have chosen a new worksheet.

  • Then click on OK. Owing to the previous choice, a new spreadsheet will open now.
  • Now go to the PivotTable Fields that you will find on the right side of the spreadsheet containing the pivot table.
  • Next, click and drag the Date of Publication to both Rows and Values fields individually.

pivot table fields for excel count date occurrences

  • As a result, the pivot table will appear in the desired place.

excel count date occurrences using pivot table


2. Total Number of Dates in a Particular Range

In this section, we will continue to count date occurrences in Excel. But this time, we will count the dates that belong to a range instead of a single match like in the previous section.

To demonstrate the methods, we will use the following dataset.

excel count date occurrences from a range

There is a change in the dataset. Although the main idea is the same as the previous one, some functions and formulas do not function properly with dates after the year 1901. And thus such change in the dataset.

Either way, you can follow one of these methods to count the date occurrences in a particular range by one of these three ways.


2.1 Using COUNTIFS Function

In this subsection, we will utilize the COUNTIFS function to count date occurrences from a particular range in Excel. This function takes several arguments- always a range and a condition in pairs. And then it returns the number of cells that represents all the given conditions. So we can easily employ this function to our advantage.

Follow these steps to see how we can do that.

Steps:

  • First, select cell E5.
  • Then write down the following formula.

=COUNTIFS(C5:C17,">1/1/1940",C5:C17,"<=12/31/1950")

  • Finally, press Enter.

excel count date occurrences using countifs function for range application

So we can see there are a total of three books that range from the year 1940 to 1950. And this is how we can count date occurrences from a particular range in Excel.


2.2 Using SUMPRODUCT Function

Another way we can do the same is to use the SUMPRODUCT function. Generally, we use this function to find the sum of the products of several arrays. And we put these arrays as arguments of the function.

Now let’s suppose we want to find the same result- books published between 1940 and 1950. So what we can do is find the arrays of the books that are published after 1940 and before 1950 and then use the SUMPRODUCT function to find the sum of the product of them. Which in turn will give us the total number of books or dates that occurred within that period.

Follow these steps to see how we can implement that.

Steps:

  • First, select cell E5.
  • Second, insert the following formula in the cell.

=SUMPRODUCT(((C5:C17)>=DATE(1940,1,1))*((C5:C17)<=DATE(1950,12,31)))

  • Third, press Enter.

excel count date occurrences within a range using sumproduct function

This is how we can use the function to count date occurrences from a particular range in Excel.


2.3 Using VBA Code

And the final method to count the date occurrences from a particular range would be the usage of a VBA code. Microsoft’s Visual Basic for Applications (VBA) is an event-driven programming language that we can use for various purposes- starting from simple cell entries and modifications to automate large and dull processes.

In this section, we will talk about the code that will help us count dates that belong to a range. But before using any kind of VBA code, you first need the Developer tab to show on your ribbon. If you don’t have one already, click here to see how to display the Developer tab on your ribbon.

Before we dive into the details of the process, let’s prepare the dataset for the process. This is plainly to showcase the length of the code, although you can use them with the same datasets above. We are showing the application for the following dataset with a date range for each book.

Once you have the tab, you can follow these steps to count date occurrences that belong to a particular range within Excel.

Steps:

  • First, go to the Developer tab on your ribbon.
  • Then select Visual Basic from the Code group section.

opening vba

  • As a result, the VBA window will open up.
  • Now select the Insert tab in it.
  • Then select Module from the drop-down menu.

inserting module in vba

  • If the module isn’t already selected, select it.
  • Next, insert the following code in the module. It will define a new function.
Public Function CountFor(ByVal calendarDate As Date, ByVal eventDates As Range) As Long
    Dim dates As Variant
    dates = eventDates.Value

    'assert eventDates consists of 2 columns
    Debug.Assert UBound(dates, 2) = 2
    Const StartDateColumn = 1
    Const EndDateColumn = 2

    Dim result As Long

    Dim eventIndex As Long
    For eventIndex = LBound(dates, 1) To UBound(dates, 1)
        If dates(eventIndex, StartDateColumn) <= calendarDate And dates(eventIndex, EndDateColumn) >= calendarDate Then result = result + 1
    Next

    CountFor = result

End Function
  • Now close the module and go back to the spreadsheet.
  • After that, select a cell you want to store the value (cell F5 in our case) and insert the following formula.

=CountFor(DATE(90,1,1),C5:D24)

  • Finally, press Enter.

excel count date occurrences using custom vba function

Thus we can use VBA to our advantage to create a custom function and use it as many times as we want in the workbook to count date occurrences within a particular period in Excel.


3. Count Unique Date Occurrences

As part of counting the number of dates, we will now demonstrate a method to count all the unique dates and the number of occurrences for each one in Excel. We are gonna need this method for a dataset like this.

As you can see, there are some repetitive dates. We are gonna count exactly what dates are there and how many times. We are gonna need the usage of UNIQUE and COUNTIF functions for that.

Follow these steps to see how we can utilize them.

Steps:

  • First, let’s find out the unique dates. For that, select cell
  • Then write down the following formula in it.

=UNIQUE(C5:C17)

  • As a result, it will create an array with all the unique values from the range.

excel count unique dates

  • Now to find the counts, select cell F5 and insert the following formula.

=COUNTIF($C$5:$C$17,E5)

  • Then press Enter.

excel count unique date occurrences

  • After that, select the cell again and click and drag the fill handle icon to the end of the unique values to replicate the formula for the rest of the cells.

As a result, Excel will provide us with all the unique dates and date occurrences of each one.


Download Practice Workbook

You can download the workbook used for the demonstration from the link below.


Conclusion

So this was all about counting the number of dates. Hopefully, you have grasped the idea and can apply them to count date occurrences for your circumstances in Excel. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.


Related Articles


<< Go Back to Excel COUNT Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo