How to Count Date Occurrences in Excel (3 Methods)

Method 1 – Number of Occurrences of a Particular Date

1.1 Using the COUNTIF Function

Steps:

  • Select cell E5.
  • Enter the following formula:

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

  • Press Enter.

excel count date occurrences using countif function


1.2 Using the SUMPRODUCT Function

Steps:

  • Select cell E5.
  • Enter the following formula:

=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, it 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 1s and 0s. This is the total number of cells with the date August 18, 1888.

  • Press Enter.

excel count date occurrences using sumproduct funtion


1.3 Using a Pivot Table

Steps:

  • Select any cell from the dataset.
  • Go to the Insert tab on your ribbon.
  • Select PivotTable from the Tables group section.

  • The dataset range and where you want to put the pivot table are in the next box. We have chosen a new worksheet to demonstrate.

  • Click on OK. A new spreadsheet will open.
  • Go to PivotTable Fields.
  • Click and drag the Date of Publication to both Rows and Values fields individually.

pivot table fields for excel count date occurrences

  • The pivot table will appear in the desired place.

excel count date occurrences using pivot table


Method 2 – Total Number of Dates in a Particular Range

2.1 Using the COUNTIFS Function

Steps:

  • Select cell E5.
  • Enter the following formula:

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

  • Press Enter.

excel count date occurrences using countifs function for range application

There are three books issued between 1940 and 1950.


2.2 Using the SUMPRODUCT Function

Steps:

  • Select cell E5.
  • Enter the following formula in the cell:

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

  • Press Enter.

excel count date occurrences within a range using sumproduct function


2.3 Using VBA Code

Steps:

  • Go to the Developer tab on your ribbon.
  • Select Visual Basic from the Code group section.

opening vba

  • The VBA window will open up.
  • Select the Insert tab in it.
  • Select Module from the drop-down menu.

inserting module in vba

  • If the module isn’t already selected, select it.
  • Enter 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
  • Close the module and go back to the spreadsheet.
  • 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)

  • Press Enter.

excel count date occurrences using custom vba function

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.


Method 3 – Counting Unique Date Occurrences

Steps:

  • Select a cell.
  • Enter the following formula:

=UNIQUE(C5:C17)

  • It will create an array with all the unique values from the range.

excel count unique dates

  • To find the counts, select cell F5 and insert the following formula:

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

  • Press Enter.

excel count unique date occurrences

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


Download the Practice Workbook


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
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