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

**Table of Contents**hide

**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)`

- 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 Functio**n

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))`

- Now, press the
**ENTER**key.

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

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))`

- 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:**

**How to Count Cells with Specific Text in Excel (Both Case Sensitive and Insensitive)****Count Cells that Contain Specific Text in Excel**

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

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

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)))`

- Now, press the
**ENTER**key.

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

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: Create User Defined Function to Count Number of Cells with Dates**

In this method, we generate a user-defined function using VBA. Let’s follow the procedures down.

**Steps:**

- Firstly, go to the
**Developer**tab from the ribbon. - Secondly, click on
**Visual Basic**from the**Code**category to open the**Visual Basic Editor**. Or press**ALT+F11**to open the**Visual Basic Editor**.

- This will appear in the
**Visual Basic Editor**where we write our codes. - Thirdly, click on
**Module**from the**Insert**drop-down menu bar.

- This will create a
**Module**in your workbook. - And, 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.

- Further, go back to your worksheet and insert the formula there.

`=Count_DateCells(D5:D12)`

- Press
**Enter**. - That’s it! you will get your result.

**Method 7: 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**.

- After that
**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
```

- After that, press
**F5**or the play button to**run**the code. - At this point, Enter the formula in cell
**F5**.

`=SUM(IF(Date_Count(D5:D12)=7,1,0))`

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

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

The SUMPRODUCT was exactly what I needed and I’d never used it before.

Thanks

Hello,

BILL SHIELDS!Thanks for your appreciation!

Stay connected with

Exceldemy.Good Luck!

Regards,

Sabrina Ayon.Author, ExcelDemy

Your vba code is too complex. Your code outputs an array that has to be fed further into a complex formula and you didn’t explain why do we have to use 7,1,0 with SUM IF.

Instead I would prefer to use isDate() function inside the loop to check the cells for being an actual date and increment the count variable each time you get a TRUE result. Finally return a count of cells containing the date instead of an array which will have to be used with more functions.

Simplicity should be the key. The code should be like this as per my suggestion;

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

Hello,

EHTISHAM SAFDAR!Thanks a ton for your suggestion!

In

Method-7, the precise range we require to count the number of cells containing dates isD5:D12. Determines whether each data value in a given array or range is legitimate bySUMeach one.Regards,

Sabrina Ayon.Author, ExcelDemy