If you wish to learn how to count dates in Microsoft Excel, follow along with the techniques that I’ve outlined in this effortless guide.

You must have tried counting specific dates in a dataset, eventually knowing how tricky the task could be. Mostly, you often struggle with mismatched formats and hidden errors. Or, you just don’t know the right formula. But don’t worry—I’ve got you covered. In this Excel guide, I’ve outlined the most common examples to count dates in Excel with easy steps. Let’s dive in and make Excel work for you!

Count Total Dates in a Column

If you simply need to count the total number of cells in a dataset containing date entries, you can use the COUNT function.

Select a cell where you want to calculate the total dates and enter the following formula:

=COUNT(C2:C9)

You need to change the cell range reference in the above formula and make sure it matches the source data.

Hit Enter to calculate the cell.

You should now see the total count of dates in the data range that you have entered in the formula syntax.

Please note that the total count includes any numerical values, including dates. So, the source dataset mustn’t have any numerical values except date entries.

Count Cells With Dates in Them

Suppose your data source has a column that contains a mixed value of dates, numeric values, and text strings. From this column, you wish to count only those cells that have dates. Here, you can use the COUNTIF function to exclude all values but date entries.

Select a cell in your worksheet where you wish to populate the counted dates.

Enter the following formula into the cell and press Enter:

=COUNTIF(C2:C9,">10000") 

Make sure you change the cell range references to match your own dataset.

Excel will only count the dates from the selected cell range.

Count Dates Based on a Condition

Suppose you’ve got a dataset with a Status and Date column. You wish to count those dates for which the status is Active. Refer to the screenshot above for the structure of the dataset needed to follow this method.

Select the cell where you want the dates to be counted and enter the following formula into it:

=COUNTIFS(B:B, "Active", C:C, "<>")  

In the formula given above, B:B stands for the entire column B and C:C refers to the whole column C. You only need to change these column references as per your own dataset.

The codelet Active is for the status you want to use as the condition to count dates. You can change this to any other conditional statement according to your own dataset.

Keep in mind that the text string you use in place of Active must be present in column B for the formula to work seamlessly.

Press Enter to calculate the dates that have the Active status in column B.

That’s it! You’ve successfully calculated dates with a condition in another column.

Now, you can make the conditional statement dynamic by using a cell reference instead of a hard-coded text string, which is the word Active in the formula syntax.

For example, select a cell in the worksheet, like E2, where you’ll enter the condition to be used from column B to count the dates in column C.

Now, use the following formula instead:

=COUNTIFS(B:B, E2, C:C, "<>")

Make sure you customize the cell range references to match your own dataset.

Hit Enter to calculate the selected cell.

Excel will use the condition stated in the cell E2 to count the dates from column C.

Count the Number of Times a Date Appears

Sometimes, you need to count how many times a specific date shows up in a dataset. You can easily count that using a simple Excel formula instead of counting the date manually or applying a complicated data analytics approach.

Let’s say you wish to find out how many times the date 4/5/2025 appears in the Exam Date column of the dataset shown above.

Select a cell where you wish to populate this date count and type in the following formula into the selected cell:

=COUNTIF(C2:C7, "4/5/2025")

You must change the data source reference in the above formula, which is C2:C7. Use a cell range as per your own dataset.

Hit Enter to calculate the cell.

Excel will only count the cells in column C that have the date entry 4/5/2025 in them.

You can further improve the formula by making it dynamic. Here’s how:

Instead of putting 4/5/2025 as a hard-coded condition in the formula syntax, include a cell range reference as the criteria argument for the COUNTIF function.

Here’s the improved formula:

=COUNTIF(C2:C7, E2)

Now, if you enter a date in that cell range, Excel will only count that date. This way, you won’t have to manually change the conditional text string in the COUNTIF formula.

Count Dates From a Specific Year

When you’re working with a column of data containing dates from different years, and you wish to count only dates from a specific year, you can use this method. Here, I’ll show you how to use the COUNTIFS function to accomplish this task.

COUNTIFS counts values from multiple cell ranges and criteria. Therefore, you can use it to create a range of years, say 1/1/2025 to 12/31/2025 so that Excel will count only the dates that fall in this range.

Go to the destination cell where you want the dayte count. Enter the following formula into this cell:

=COUNTIFS(C2:C7,">=1/1/2025",C2:C7,"<=12/31/2025")  

In the formula syntax stated above, make sure you customize the cell range references as well as the date range so the output value is according to your own dataset.

Hit Enter to calculate the cell.

Excel will count only those days that fall within the date range that you’ve indicated in the formula.

Count Unique Dates (No Duplicates)

Sometimes, you need to count unique dates when certain events have occurred, like an employee punching in for attendance, a customer visiting your online or physical shop, unique days when project milestones were achieved, and so on.

Here, you can combine the COUNTA function with the UNIQUE function to count unique days in a dataset.

Copy the following formula and paste it into a cell where you want to get the unique date count:

=COUNTA(UNIQUE(FILTER(C2:C8, C2:C8>10000)))

Customize the cell range reference given in the above formula.

Hit Enter to get the unique date counts in the selected cell.

Excel will only count those cells that are unique in the column.

Count Weekdays vs. Weekends

Suppose you need to calculate overtime for workers who have worked on weekends. You’ve got a long list of dates in a column generated from the attendance tracking machine. You can’t just go through all the dates one by one and match those in a calendar to find out the weekends. You can use an Excel formula made up of SUMPRODUCT, ISNUMBER, and WEEKDAY to automate this task and save hours.

Enter the following formula into a cell where you wish to count the weekends from a column of dates:

=SUMPRODUCT((ISNUMBER(C2:C7))*(WEEKDAY(C2:C7,2)>5))

Ensure you change the cell range reference in this formula to match your own dataset.

Hit Enter to calculate the selected cell.

Excel will quickly count all the dates in the column that fall on weekends.

Count Dates Using Power Query

If you simply need to count the total number of date entries in a long column containing non-date values as well, you can use Power Query.

A regular Excel worksheet can only accommodate data in 1,048,576 rows and 16,384 columns. If your source dataset exceeds these numbers, you’ll need Power Query. Also, when you analyze a large dataset in Power Query, it doesn’t slow down your Excel workbook, while a medium-sized Excel workbook can easily reduce the performance of the entire app.

So, you can import your data into Power Query, get the value you want, and close it. Also, you can create a data connection for future date count calculations.

To import a dataset from an external source, go to the Data tab and click on the Get Data command.

Hover the cursor over your preferred external data source, like From Database, From Azure, etc.

An overflow menu will open on the right side. Click on the final data connector from this context menu, like From Azure SQL Database.

Follow the onscreen instructions to complete the data import process.

If you’re exporting a dataset from the active Excel worksheet, select the data and click on the From Table/Range command.

Click OK on the Create Table dialog box to finalize the data export.

Your data will be loaded and opened in the Power Query Editor interface.

Click on an existing step below the Applied Steps column on the right side.

Now, click on the fx icon in the Power Query formula bar.

A custom step will be added in the Applied Steps column.

Now, click on the formula bar and copy and paste the following formula into it:

=Table.RowCount(Table.SelectRows(Source, each [Exam Date] <> null))

Change Exam Date to the actual column header name of the source dataset.

Hit Enter, and you’ll get the total date count.

Conclusion

Now you know how to count dates in Microsoft Excel using various Excel functions. You’ve also learned how to use Power Query to count dates.

If you’ve liked the guide, share an acknowledgment by commenting below, and don’t forget to share it with your friends and family members. If you’ve any suggestions to improve the guide, you can comment as well.