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

Excel sheets can be used to keep track of time, date, schedule events, and activities, keep track of objects and their prices, and much more. In this article, we will see how to count number of cells with dates in Excel using formulas and VBA. For your better understanding, we will use a sample dataset containing Name, Gender, and Date of Birth.

count number of cells with dates in Excel


Download Practice Workbook


6 Ways to Count Number of Cells with Dates in Excel

There are several ways to count the number of cells with dates in Excel. We will see the use of VBA, COUNTA, COUNTIFS, SUMPRODUCT, and a combination of functions throughout this post.


Method 1: Count Number of Cells with Dates Using COUNTA Function

The COUNTA function is helpful to calculate the number of cells containing numeric values.
Steps:

  • First, click on cell F5 and type the following formula.
=COUNTA(D5:D12)

Count cells with Dates using COUNTA

  • Now, press the ENTER key.

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

Related Content: Excel Count Cells with Numbers (5 Simple Ways)


Method 2: Count Dates in a Given Year Using SUMPRODUCT Function

In our dataset, there are different dates in different years. What should we do, if we want to know dates in specific years? Let’s see, how to do it.
Steps:

  • First, click on cell F5 and type the formula as follows.
=SUMPRODUCT(--(YEAR($D$5:$D$12)=$F5))

Count cells with Dates by SUMPRODUCT Function

  • Now, press the ENTER key.

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

Count cells with Dates in Excel by Formula

So, what is happening here?
To simplify, in this formula, the YEAR function will extract all the years from the valid date range D5:D12 and will match it with the year given in cell F5.

=SUMPRODUCT(--(YEAR(1995;1994;1993;1992)=1992))

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

={FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE}

As a result, it will count the TRUE which is 2 in the case of the year 1992.
Then, once we use the AutoFill, the criteria value gets changed so does the YEAR function’s result.

Related Content: How to Count Blank Cells in Excel with Condition (3 Methods)


Method 3: Count Number of Cells with Dates Using Combination of Functions

Let’s, try another way to count number of cells with dates. This time we will use a combination of functions to count the number of dates in cells in Excel.
Steps:

  • First, click on cell F5 and type the following formula.
=SUM(IF(ISERROR(DATEVALUE(TEXT(D5:D12, "dd/MM/yyyy"))), 0, 1))

count number of cells with dates in Excel

  • Now, press CTRL+SHIFT+ENTER altogether. If you’re using Excel 365 then pressing ENTER will do the task for you.

That’s it.
Here the ISERROR function will see, whether the cells have number values. It will tell FALSE if the cell is not blank and TRUE in the case of blank cells. Then, the IF function will SUM 1 for each FALSE value, zero for TRUE.

Read More: Count Empty Cells in Excel (4 Ways)


Similar Readings:


Method 4: Count Dates in Current Month Using COUNTIFS Function

Now, we will see how to count dates in the current and previous months using the COUNTIFS function. We got a dataset where dates of joining are given. We want to see how many joining dates are in the current month and how many are in the previous month.

Count cells with Dates for current and previous month in Excel
Steps:

  • First, click on cell G5 and type the formula as shown below.
=COUNTIFS(D5:D12,">="&EOMONTH(TODAY(),-1)+1,D5:D12,"<"&EOMONTH(TODAY(),0)+1)

  • Now, press the ENTER key.

count number of cells with dates for current month

So, we get result 5. It’s also visible from the dataset that in our current month March, the total dates are 5.
After that, we will see how to calculate dates in the previous month.

Steps:

  • First, click on cell H5 and type the formula as shown below.
=COUNTIFS(D5:D12,">="&EOMONTH(TODAY(),-2)+1,D5:D12,"<"&EOMONTH(TODAY(),-1)+1)

  • Finally, press the ENTER key and our result is ready.

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.

Read More: How to Count Filled Cells in Excel (5 Quick Ways)


Method 5: Count Birthdays by Month Using SUMPRODUCT Function

In this method, we will see birthdays by month using the SUMPRODUCT function.
Steps:

  • Type the following formula in cell G5.
=SUMPRODUCT(--(MONTH($D$5:$D$12)=MONTH($F5&1)))

count number of cells with dates in Excel

  • Now, press the ENTER key.

Count Birthdays by month

  • After that, drag down to AutoFill for the rest of the series.

Count cells with birthdates

Can you relate how the SUMPRODUCT function works here?. We think you do, it works in the same way we’ve discussed in Method 2.

Read More: How to Count Blank Cells in Excel (5 Ways)


Method 6: Count Number of Cells with Dates Using VBA

Finally, in this method, we will see how to count the number of dates in Excel using VBA.

Steps:

  • First, right-click on the sheet and go to View Code.

count number of cells with dates in Excel VBA

  • After that copy and paste the VBA code below.
    VBA Code:
Option Explicit

Function DateCells(dRanges As Range) As Variant

Dim darr() As Variant
Dim drng As Range
Dim dcount As Integer

Application.Volatile

ReDim darr(dRanges.Cells.Count - 1) As Variant

dcount = 0

For Each drng In dRanges

darr(dcount) = VarType(drng)
dcount = dcount + 1

Next

DateCells = darr

End Function

Count cells with Dates with vba

  • After that, press F5 or the play button to run the code.
  • At this point, Enter the formula in cell F5.
=SUM(IF(DateCells(D5:D12)=7,1,0))

Count cells with Dates using vba

  • Finally, press the CTRL + SHIFT + ENTER keys while doing so.

Through this code, we are creating a user function called DateCells. This function will check date values in a given array or ranges and SUM them one by one if the date value is valid.

Read More: How to Count Odd and Even Numbers in Excel (3 Easy Ways)


Practice Section

The single most crucial aspect in becoming accustomed to these quick approaches is practice. As a result, I’ve attached a practice workbook where you may practice these methods.

Count cells with Dates practice


Conclusion

These are 6 different ways to count number of cells with dates in Excel. Based on your preferences, you may choose the best alternative. Please leave them in the comments area if you have any questions or feedback.


Related Articles

Mahbub
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo