7 Easy Ways to Count Number of Cells with Dates in Excel

If you are looking to count number of specific cells containing dates in Microsoft Excel, then you’ve come to the right place. There are several ways to count number of exact cells containing dates in Microsoft Excel and here, we’ll try to talk about the 7 quickest ways to count number of specific cells containing dates in Microsoft Excel. Here is an example of our final output.

📁 Download Excel File

Download this practice book.

Learn to Count Number of Cells with Dates in Excel with These 7 Easy Methods

Excel sheets can track time, date, events, activities, objects, pricing, and more. This article shows how to count Excel cells with dates using formulas and VBA. This is the sample dataset including employee name, joining date, and leaving date we will be working with that can help you understand.

In the first method, we will use COUNTA function here. COUNTA function can help you figure out how many cells have numbers in them. This method works for columns only containing date values and not any other types. So it might be helpful if you have such a dataset and want to count number of entries there. However, if you have a mix of all types, other methods would be a better choice.

The steps are given below.

⬇️⬇️ STEPS ⬇️⬇️

• At first, we select cell F6. Then we put this formula below.

• Then we press Enter. The output will be like this.

 Read More: 5 Examples to Count Filled Cells Using VBA in Excel

Let’s try a different way to figure out how many cells have dates. This time, to count number of dates in cells, we will use a combination of functions such as SUM, IF, and ISERROR functions. In addition, we need the usage of DATEVALUE and TEXT functions too.

IF function enables you to compare a value to what you anticipate logically. We will use SUM function here also. The SUM function adds numbers together. You can add single values, references to cells or ranges, or a combination of all three. With ISERROR function, you can find out if a numeric expression is an error. If the expression argument points to an error, ISERROR returns TRUE. If not, it will return FALSE.

When a worksheet has dates in text format that we wish to filter, sort, format as dates, or use in date calculations, the DATEVALUE function can help. Finally, by using format codes, we can change the way a number looks by using the TEXT function on it.

⬇️⬇️ STEPS ⬇️⬇️

• At first, we select cell F6. Then we put this formula below.

• Then we press Enter. The output will be like this.

Read More: 3 Ways to Count Blank Cells with Condition in Excel

Here we are going to count the leaving dates of the employees by month. To count the dates, we can only use the SUMPRODUCT function.

The SUMPRODUCT function counts and adds up all the ones and zeros in an array. The MONTH function used here will return a serial number’s month. Months range from 1 (January) to 12 (December).

⬇️⬇️ STEPS ⬇️⬇️

• At first, we select cell G6. Then we put this formula below.

• Then we press Enter. Then we drag down the formula.

• The output will be like this.

Here we are going to count the leaving dates of the employees by year. To count the dates, we can only use the SUMPRODUCT function.

The SUMPRODUCT function counts and adds up all the ones and zeros in an array. YEAR function used here will return a serial number’s year. Months range from 1 (January) to 12 (December).

⬇️⬇️ STEPS ⬇️⬇️

• At first, we select cell G6. Then we put this formula below.

• Then we press Enter. Then we drag down the formula.

• The final output will be like this.

In this method, we will use another combination for the purpose. First, COUNTIFS function will count the total date numbers in the running month. The dataset contains joining dates. We hope to determine occurrence of joining dates that are this month. For this purpose, we will use TODAY function. It returns the current date’s serial number. Excel calculates the date and time using the serial number as the date-time code. Finally, we will use EOMONTH function. The serial number for the month’s last day is returned. EOMONTH calculates maturity or due dates on the final day of the month.

⬇️⬇️ STEPS ⬇️⬇️

• At first, we select cell G6. Then we put this formula below.

• Then we press Enter. The output will be like this.

In this method, we will use COUNTIFS function. COUNTIFS function will count the dates in previous months. The dataset contains joining dates. We are willing to determine joining dates existing in this month. For this purpose, we will use TODAY function. It returns the current date’s serial number. Excel calculates the date and time using the serial number as the date-time code. Finally, we will use EOMONTH function. The serial number for previous month’s last day is returned. EOMONTH calculates maturity or due dates on the final day of previous month.

⬇️⬇️ STEPS ⬇️⬇️

• At first, we select cell G6. Then we put this formula below.

• Then we press Enter. The final output will be like this.

Now we will use VBA to compare duplicate rows. Before talking about the method, first, we have enabled Developer Tab on our Ribbons. You can follow the link to see the process of enabling the Developer Tab by clicking here. Let’s follow the steps below:

⬇️⬇️ STEPS ⬇️⬇️

• Let’s press ALT+F11 to bring up the VBA Window. You can also do this by going to Developer tab, then clicking on Visual Basic.

• Then we choose Module from Insert from the menu. This will bring up the window for the VBA Module. Here is where we will put our code.

• Now we type the code below:

Option Explicit

Function DatewithCells(dRanges As Range) As Variant
Dim Date_Arr() As Variant
Dim Date_Rng As Range
Dim Date_Count As Integer
Application.Volatile
ReDim Date_Arr(dRanges.Cells.Count - 1) As Variant
Date_Count = 0

For Each Date_Rng In dRanges
Date_Arr(Date_Count) = VarType(Date_Rng)
Date_Count = Date_Count + 1
Next

DatewithCells = Date_Arr

End Function

• Now we save and run this code.

• After that, we select cell F6. Then we put this formula below.

• Then we press Enter. The output will be like this.

📄  Important Notes

🖊️  In Excel, the ribbon is used to access various commands. We can change a lot of things in the options dialogue window, like the ribbon, formulas, proofing, saving, and so on. So the appearance of worksheets on your device could be different from ours.

🖊️  A workbook is given containing all the discussed methods so that you can practice yourself.

🖊️  All these exercises and tutorials are done on Microsoft Office 365. Some functions may be unavailable in previous versions.

🖊️  At the side of each Excel worksheet file, there is space where they can practice.

📝  Takeaways from This Article

📌 Readers will be able to count number of distinct cells containing dates in Microsoft Excel with various methods.

📌  They can do this by using SUMPRODUCT function in order to count by year.

📌  Again, they can do the same by using SUMPRODUCT function in order to count by year.

📌  Moreover they can implement COUNTA Function.

📌  Besides, the readers can use a combination of functions to count the dates.

📌  Finally they can implement VBA for this purpose.

Conclusion

That concludes today’s session. These are the methods for counting the number of specific cells with dates in Microsoft Excel with various methods. We are hopeful that this article will be extremely beneficial to you. Don’t forget to leave your thoughts and questions in the comments section and explore our website, ExcelDen.com, the best Excel solutions provider.

Related Article

• 3 Easy Ways to Count Odd and Even Numbers in Excel