How to Count Number of Cells with Dates in Excel (7 Ways)

Let’s use a sample dataset containing Name, Gender, and Date of Birth to showcase how you can count how many cells contain dates in Excel.

Method 1 – Using the COUNTA Function to Count the Number of Cells with Dates in Excel

Steps:

• Click on cell F5 and copy the following formula.

• Press Enter.

Here, Excel has counted all the numeric date values ranging from D5 to D12.

Method 2 – Counting Dates in a Given Year Using the Excel SUMPRODUCT Function

Let’s count how many cells fall into specific years. We’ll use two helped columns, with the first containing the years from the range and the other showing the results.
Steps:

• Click on cell F5 and copy the following formula:

• Press Enter.

• AutoFill by dragging down the right button on the mouse.

The YEAR function will extract all the years from the valid date range D5:D12, and the equals operand will match it with the year given in cell F5.

To get an array of TRUE and FALSE, each date is compared to the year value in Column D.

This will count the TRUE which is 2 in the case of the year 1992.
Once we use the AutoFill, the criteria value gets changed to the respective cell in the helper column.

Method 3 – Combining Excel Functions to Count the Number of Cells with Dates

Steps:

• Click on cell F5 and copy the following formula.

• Press Ctrl + Shift + Enter altogether. If you’re using Excel 365, pressing Enter will do.

Here the ISERROR function will check whether the cells have number values. It will output FALSE if the cell is not blank and TRUE for blank cells. Then, the IF function will SUM 1 for each FALSE value and 0 for TRUE.

Method 4 – Counting Dates in Current Month Using the COUNTIFS Function

We have a dataset where dates of joining are given. We want to see how many dates are in the current month and how many are in the previous month.

Steps:

• Click on cell G5 and copy the formula below:

• Press Enter.

• In cell H6, copy the following formula.

• Hit Enter.

This formula relies on the COUNTIFS function to count dates that are greater than or equal to the current month’s first day and less than the next month’s first day. Both dates are created using the EOMONTH function, which takes the current date from the TODAY function.

Method 5 – Computing Birthdays by Month Using the SUMPRODUCT Function

Steps:

• Copy the following formula in cell G5.

• Press Enter.

• Drag down to AutoFill for the rest of the series.

Method 6 – Creating a User Defined Function to Count the Number of Cells with Dates

Steps:

• Go to the Developer tab from the ribbon.
• Click on Visual Basic from the Code category to open the Visual Basic Editor or press Alt + F11.

• Click on Module from the Insert drop-down menu bar. This will create a Module in your workbook.

• Copy and paste the VBA code shown below:

VBA Code:

Option Explicit
Function Count_DateCells(dRanges As Range) As Integer
Dim drng As Range
Dim dcount As Integer
Application.Volatile
dcount = 0
For Each drng In dRanges
If IsDate(drng) Then
dcount = dcount + 1
End If
Next
Count_DateCells = dcount
End Function

• Press Ctrl + S to save the file.

• Go back to your worksheet and insert this formula in the result cell.

• Press Enter.

Method 7 – Applying a VBA Macro Tool to Count the Number of Cells with Dates

Steps:

• Right-click on the sheet and go to View Code.

• Copy and paste the VBA code below:

VBA Code:

Option Explicit
Function Date_Count(dRanges As Range) As Variant
Dim dCell() As Variant
Dim rg As Range
Dim dcnt As Integer
Application.Volatile
ReDim dCell(dRanges.Cells.Count - 1) As Variant
dcnt = 0
For Each rg In dRanges
dCell(dcnt) = VarType(rg)
dcnt = dcnt + 1
Next
Date_Count = dCell
End Function

• Press F5 or the Play button to run the code.
• Enter this formula in cell F5:

• Press the Ctrl + Shift + Enter keys to apply it.

This function will check date values in a given array or ranges and SUM them one by one if the date value is valid.

Download Practice Workbook

<< Go Back to Count Cells | Formula List | Learn Excel