How to count cells with dates in Excel

Microsoft Excel is known for handling numeric and text data and performing all sorts of calculations on the data as well. It has many other functions related to date data to create, calculate, add and subtract the dates. However, when it comes to counting the cells with dates in them there is no straightforward solution to it.

In this tutorial, we’ll learn how to count cells with dates using the following two methods;

• Using COUNTIF with ISNUMBER and DATEVALUE functions
• VBA code to count cells with dates

Let’s see the given dataset which contains the dates along with some text, numbers and wrong dates as well as shown below;

We can see in the dataset that Not paid (text), 22/25/2022 (wrong date) and 252 is simply a numeric value. None of these are dates, however, the values in D2, D4 and D5 are proper dates. So, our working solutions should produce a count of 3 when applied to this range D2:D7. 

Method 1: Use COUNTIF with ISNUMBER and DATEVALUE

There is no straightforward solution to counting the cells with dates in them. So, we’ll use a combination of three formulas. First, we’ll use the DATEVALUE function. The DATEVALUE function returns a number if a valid date is passed to it. However, it requires the date in Text format. Therefore, we’ll join the cell values of range D2:D7 with an empty string using the & ampersand sign. If a valid date will be encountered then we get a number as a result. 

We will check if the result is a number or not by using ISNUMBER on the complete range D2:D7 and as a result get an array of TRUE and FALSE. Each TRUE then represents a valid date and each FALSE represents any text, simple number and wrong date. However, we’ll have to store these results in a helper column which in our case will be E2:E7.

After this we can simply use COUNTIF(E2:E7, “TRUE”) which will give the exact number of cells containing the dates. This will also take care of blank cells in between. We’ll always get a FALSE for the blank cells. Now that we understand how the formula will work. Let’s implement this in the following steps.

Step 1 – Create a helper column using ISNUMBER and DATEVALUE

• First, we’ll create a helper column using the following formula in cell E2.

=ISNUMBER(DATEVALUE(“”&D2))

• Double click on the fill handle to implement the formula to the whole data.

Step 2 – Combine the formula with COUNTIF

• Now, combine the formula results with COUNTIF using the following formula

=COUNTIF(E2:E7,TRUE)

• This will give us the exact count of 3 as explained in the aforementioned section.

Method 2: Use VBA Code

The second method is to use a custom VBA code. Let’s do this by following these simple steps,

Step 1 – Open the VBA Editor by pressing ALT+F11 or through Developer Tab 

• To open up the VBA Editor, go to the Developer tab and click on the Visual Basic option.
• We can also use the shortcut key of ALT+F11 to do the same.

Step 2 – Insert the blank module and write code

• Go to the Insert Menu and click on Module.
• This will insert a blank module into the VBA Editor. Copy the code from the end of the tutorial and paste it in the Editor.
• Click on the Save button. 
• Excel will ask you to save the file as .xlsm Macro Enabled Excel File. 
• Click OK and save the file.

As we have implemented the code as a VBA Function, this gives us the liberty to write User Defined Functions (UDF). Once coded properly, we can use these just like Excel’s built-in Functions. The name of our function is Count_DateCells(dRange As Range). We can pass a range of cells to it and it will return a number to us after counting the cells with dates.

Step 3 – Implement the code to count the dates in a given Range

• Now write the following formula in any appropriate cell and get the desired result.

=Count_DateCells(D2:D7)

The Code Snippet with Explanation:

The logic used in the code is very simple. The function requires a range of cells that contains the data. The code implements a For Each loop which iterates over each cell in the given range and checks if the cell contains a date or not through VBA’s built-in function isDate(range). If a valid date is found the counter variable dcount is incremented by 1 each time. Hence at the end of the loop our variable dcount will hold the count of the cells containing dates only. All cells with text, blanks or simple numeric or alphanumeric values will be ignored.