To count cells between two dates in Excel, use COUNTIFS with two criteria: =COUNTIFS(range,">="&start_date, range,"<="&end_date). For example, =COUNTIFS(A2:A20,">=2024-01-01",A2:A20,"<=2024-01-31") counts all dates in January 2024. This works in Excel for Microsoft 365, Excel 2021, and older versions.
Why COUNTIFS Is the Standard Solution
Excel offers several counting functions, but COUNTIFS is the most flexible for date ranges because it accepts multiple criteria. A single COUNTIF can only test one condition, so it cannot directly check "between" two dates. COUNTIFS lets you add a lower bound and an upper bound, and it handles dates stored as real Excel dates (serial numbers) or as text that Excel recognizes. The function is available in Excel 2007 and later, including Excel for Mac and Excel for the web, though the web version may require you to use the same syntax.
Step-by-Step: Using COUNTIFS for a Date Range
Assume your dates are in column A, rows 2 through 20, and you want to count dates from January 1, 2024, to January 31, 2024. Follow these steps:
- Click an empty cell, for example B2.
- Type
=COUNTIFS(A2:A20,">=2024-01-01",A2:A20,"<=2024-01-31")and press Enter. - Excel returns the count of dates that fall on or after Jan 1 and on or before Jan 31.
If you have the start and end dates in separate cells (say D1 and E1), use cell references:
=COUNTIFS(A2:A20,">="&D1, A2:A20,"<="&E1)
This is safer because you can change the dates without editing the formula. In Excel for Mac, the syntax is identical; just be careful with the quotation marks and ampersand.
Using SUMPRODUCT for More Complex Conditions
When you need to count between two dates AND satisfy another condition (like a product category), you can extend COUNTIFS with more range/criteria pairs. For example, to count dates in column A that fall in January 2024 and have "Widget" in column B (B2:B20):
=COUNTIFS(A2:A20,">=2024-01-01",A2:A20,"<=2024-01-31",B2:B20,"Widget")
Alternatively, SUMPRODUCT can handle array logic without extra criteria ranges, but it is generally slower and less intuitive. Use COUNTIFS unless you need to perform calculations on the dates themselves.
Common Pitfalls and How to Avoid Them
Several mistakes cause wrong counts:
- Text dates vs. real dates: If your "dates" are actually text like "01/15/2024",
COUNTIFSmay treat them as text and fail. Convert them usingDATEVALUEor the Text to Columns wizard. - Date format mismatch: Excel stores dates as serial numbers, but the display format can be misleading. Always enter dates using the
DATEfunction or as a date literal in the formula. - Inclusive vs. exclusive: The example uses
>=and<=to include both endpoints. If you want to exclude the start or end, adjust the operators accordingly. - Whole-day boundaries: If your dates include times (e.g., 1/15/2024 14:30), a date-only comparison will miss times after midnight. Use
"<"&end_date+1to include all times on the end date.
Alternatives: Pivot Tables and Filtering
If you prefer a visual method, a Pivot Table can group dates by month or year and show counts without formulas. To do this:
- Select your data, go to Insert > PivotTable.
- Drag the date field to Rows and also to Values (it will count automatically).
- Right-click a date in the pivot table, choose Group, then select Months or Days.
Filtering is another quick option: select your date column, press Ctrl+Shift+L to add filters, then use the date filter menu to choose "Between". This shows the filtered rows, and you can read the count from the status bar. However, this is manual and not dynamic.
Counting with Dynamic Arrays (Excel 365)
If you use Excel for Microsoft 365 or Excel 2021, you can use the FILTER function to return the actual dates that fall between two dates, then wrap it in COUNTA to count them:
=COUNTA(FILTER(A2:A20,(A2:A20>=D1)*(A2:A20<=E1)))
This returns the count of non-empty cells in the filtered array. It is more flexible if you also want to sum or average the filtered values. Note that FILTER requires Excel 2021 or Microsoft 365; it will not work in Excel 2019 or earlier.
Frequently Asked Questions
How do I count dates in a specific month, like March 2024, without typing start and end dates?
Use the SUMPRODUCT with MONTH and YEAR functions: =SUMPRODUCT((MONTH(A2:A20)=3)*(YEAR(A2:A20)=2024)). This counts all dates in March 2024. It works in any Excel version, but it is an array formula, so press Ctrl+Shift+Enter in older versions (Excel 2019 and earlier) unless you use SUMPRODUCT which handles arrays natively.
Why does my COUNTIFS return 0 even though I have matching dates?
Check if your dates are stored as text. Select the date column, go to Data > Text to Columns, and choose "Date" as the column data format. Also verify that your criteria use the correct date format; Excel may interpret "2024-01-01" as text if your system uses a different date order. Use the DATE function inside the formula instead: =COUNTIFS(A2:A20,">="&DATE(2024,1,1),A2:A20,"<="&DATE(2024,1,31)).
Can I count between two dates in Google Sheets the same way?
Yes, Google Sheets uses the same COUNTIFS function with identical syntax. For example, =COUNTIFS(A2:A20,">=2024-01-01",A2:A20,"<=2024-01-31") works. The main difference is that Google Sheets may require you to use semicolons instead of commas if your locale uses them, but the default is commas.
How do I count dates that are within 30 days from today?
Use the TODAY function: =COUNTIFS(A2:A20,">="&TODAY(),A2:A20,"<="&TODAY()+30). This counts all dates from today through the next 30 days. Remember that TODAY() updates each time the workbook recalculates, so the result changes daily.
Is there a way to count unique dates between two dates?
Yes, use the UNIQUE function (Excel 365) combined with FILTER: =COUNTA(UNIQUE(FILTER(A2:A20,(A2:A20>=D1)*(A2:A20<=E1)))). This counts distinct dates in the range. In older Excel versions, you would need an array formula with FREQUENCY, but it is complex.
Quick Reference: Formula Syntax
- Basic between:
=COUNTIFS(range,">="&start, range,"<="&end) - Exclude start date:
=COUNTIFS(range,">"&start, range,"<="&end) - Include times on end date:
=COUNTIFS(range,">="&start, range,"<"&end+1) - Count with another condition:
=COUNTIFS(range1,">="&start, range1,"<="&end, range2,"condition")
Always test your formula with a small dataset to verify the results. If you need to count between two dates in a large dataset, consider using a Pivot Table for faster grouping, but for a single formula, COUNTIFS is the most direct answer.
Related
- Excel Template for Weekly Employee Schedule: How to Build One
- Excel Template for Time Sheet: How to Make One
- Excel Template for Tracking Monthly Expenses
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Java String Functions: Methods, Examples, and GuideOn W3Schools, “Java string functions” refers mainly to methods of Java’s String class, such as length(), toUpperCase(), indexOf(),…
- COUNTIF Examples: Formulas for Common Spreadsheet TasksUse Excel’s COUNTIF function to count cells in one range that meet one condition: =COUNTIF(range,criteria). For example, it…
- How to Filter Highest to Lowest in Excel: A Quick GuideTo show the largest values first in Excel, open the filter arrow on the column you want to…
- Excel Sum Multiple Cells: Formulas, Ranges, and TipsTo sum multiple cells in Excel, use the SUM function: enter =SUM(A1:A5) to add the values in cells…
- Split Numbers and Text in Excel With FormulasYes. With mixed values such as AB-123 in A2, Excel for Microsoft 365 can extract the digits with…
- Excel Vertical Lookup: VLOOKUP, XLOOKUP, and TipsAn Excel vertical lookup searches down the first column of a table for a value, then returns related…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.