How To Count Dates in Excel Based on Condition [5 Easy Ways]

If you are a project manager, then you must have a spreadsheet of all the tasks assigned to your team. It’s a compulsory duty.

The spreadsheet will contain each assigned task with a start date and an end date. You want to know how many tasks started and ended within a specific date range, let’s say November 1, 2022 to January 31, 2023.

That’s where my Excel tutorial kicks in. I will share with you five simple ways to count dates on Excel based on several conditions.

So, Let’s get started.

Excel Date Count – 5 Ways To Count Cell Number With Dates

By counting the number of cells with dates, you can easily track deadlines and progress of a project. Sometimes, you may need to include this data in reports or presentations to provide a great overview.

So, here are the ways to calculate dates in Excel:

1. Use the COUNTA Function

The COUNTA function in Microsoft Excel is used to count the number of cells that contain data in a specified range. It counts cells that contain any type of data, including numbers, text, and logical values, but excludes empty cells and cells with only spaces.

The syntax for the function is:

COUNTA(range1, [range2], …)

Where range1, [range2], … is the one or more ranges of cells to count.

Note: You have to use this function on a separate cell.

So, now look at the image down below. I have a full spreadsheet of all the records of my employees, including their date of birth at Column C. Now, I want to count the total number of dates in Column D. So I am selecting D2 as my active cell and entering the formula =COUNTA(C2:C9). Press Enter after you inserted your formula.

Note: The COUNTA function counts the number of cells that contain data, regardless of whether the data is the same in multiple cells. In other words, this function will also calculate duplicate values.

So, what to do if you want to exclude the similar data and keep the unique ones? You can use the COUNTIF function for one condition or the COUNTIFS Function for multiple conditions.

Let’s dig into it.

2. Apply the COUNTIFS Function Based on Multiple Criteria

Check the image down below. I have a dataset of dates in column A. In column B; I have three conditions. Now, try making a good Excel table and let’s check the results together.

Conditions	Formula	Description
Date > 1/3/2010	=COUNTIF(A2:A7,”>3/1/2010″)	This formula helps to count the number of cells in the range A2:A7 with dates greater than 3/1/2010
Date = 31/12/2010	=COUNTIF(A2:A7,”=12/31/2010″)	Counts the number of cells in the range A2:A7 equal to date 12/31/2010
Date >=1/1/2010 <=31/12/2010	=COUNTIFS(A2:A7,”> =1/1/2010″,A2:A7,” <=12/31/2010″)	This formula calculates the number of cells in the range A2:A7 that are between the dates 1/1/2010 and 12/31/2010
Date >= to the current month’s first day and < than the next month’s first day	=COUNTIFS(A2:A7,”>= “&EOMONTH(TODAY(),-2)+1,A2:A7,” <“&EOMONTH(TODAY(),-1)+1)	For Multiple conditions, I am using COUNTIFS to count dates in the A2:A7 that are greater than or equal to the current month’s first day and less than the next month’s first day
Date of Joining of Employees in the current month	=COUNTIFS(A2:A7,”>= “&EOMONTH(TODAY(),-1)+1,A2:A7,” <“&EOMONTH(TODAY(),0)+1)	Count dates in excel by month and year to show how many employees joined in the current month in the range A2:A7.

Note: You can also get data from another sheet and compare the dates based on cell value.

3. Use the SUMPRODUCT Function To Count Dates in a Given Year

If you want to know dates of specific years then this method is for you. Did you notice our dataset? It contained months and dates of different years.

The formula to count dates of a given year is:

=SUMPRODUCT(–(YEAR($A$2:$A$7)=$D2))

how-many-months-in-a-year-based-on-excel-condition

Let’s break down the formula for you. What’s happening here is you are using the SUMPRODUCT function to calculate the sum of the products of corresponding arrays.

Here’s a Pro TIP: Always use equals sign (=) before using a function in Excel.

You may wonder, what is the use of the double dashed operator (–)?

Well, it is used to convert a Boolean value (result of the comparison in parentheses) to a number.

The next function used here is (YEAR($A$2:$A$7)=$D2) which determines whether the year of the dates in the range $A$2:$A$7 is equal to the value in cell $D2. When you enter the formula, you must select the range of cells and also use cell references. Press Enter and use the AutoFill Option to drag the formula to the desired range.

TIP: Suppose you want to enter date automatically upon data entry, then you must use the functions NOW(), or TODAY(). These functions will help you get the exact time and date as mentioned in your system.

4. Use the Combination of DATEVALUE and ISERROR Functions

The formula I am going to use now is a combination of five different functions. The formula is:

=SUM(IF(ISERROR(DATEVALUE(TEXT(A2:A7, “dd/MM/yyyy”))), 0, 1)).

The ISERROR Function I used here is for highlighting the presence of any blank cells. If there are blank cells, the return result will show TRUE. If not, then FALSE. After that, you can either leave the cell as it is or you can replace the blank cells with 0.

All these four methods mentioned don’t deal with any code. But, if you want to automate your task by just copy and pasting codes then the next section is for you.

5. Create a VBA Code

To enable a VBA Code, you must enable the Developer Tab. After that, go to Visual Basic > Insert Module > Copy and Paste the Following Code > Press F5 to Run the Code.

The VBA code to count the number of cells with dates is as follows:

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

Now, go to your Excel Spreadsheet and type the formula =Count_DateCells(A2:A7). Change the range criteria based on your condition. The prime statement in this code is the Application.Volatile statement that ensures that Excel must recalculate the function whenever you change any worksheet values.

Note: Sometimes, due to mishandling the VBA codes, your numbers can show as text format. In that case, you can add a separate VBA code to convert text to number.

How to Count Birthdays by Month Using SUMPRODUCT Function

Sometimes, you want to surprise your colleagues with a birthday gift. On that day, there may be birthdays of two or more people. So, how many gifts should you buy?

That’s where you can use a simple formula to count how many people have their birthdays in that particular month.

The formula uses SUMPRODUCT function, and it is provided below:

=SUMPRODUCT(–(MONTH($A$2:$A$7)=MONTH($F2&1)))

Now you can easily remember how many people have their birthdays in December.

Final Words

I have mentioned 5 alternative ways to count the number of cells with dates in Excel. Now, based on your preference, you can choose the best alternative suitable for your condition.