To count dates between two limits in Excel, use COUNTIFS, which can test both the start and end dates; COUNTIF handles only one condition. With dates in A2:A100, the start date in D2, and the end date in E2, use =COUNTIFS(A2:A100,">="&D2,A2:A100,"<="&E2).
Why a date range needs COUNTIFS
Despite the common search phrase “COUNTIF date range,” the function for checking a range with two boundaries is usually COUNTIFS. COUNTIF accepts one range and one criterion, so it can count dates on or after a start date, or dates on or before an end date, but not both conditions in the same call. COUNTIFS accepts multiple range-and-criterion pairs and counts a row only when every supplied condition is true.
Excel stores real dates as serial numbers, with times represented as fractions of a day. This is why the same comparison operators used for numbers work for dates. The criteria strings in the formula combine an operator such as >= with a date value. The ampersand joins the operator to the value in the cell; it does not turn the date into text for comparison.
The two criteria ranges in a COUNTIFS formula should have the same shape. In the opening example, both refer to A2:A100. The first criterion requires each date to be at least the start date in D2, and the second requires it to be no later than the end date in E2. A row contributes one to the count only if it passes both tests.
Basic formula for dates between two bounds
Suppose A2:A8 contains seven actual Excel dates: January 2, January 5, January 8, January 10, January 12, January 15, and January 20, 2026. If D2 contains January 8 and E2 contains January 15, the inclusive formula counts four dates: January 8, 10, 12, and 15. With dates in A2:A8 and those boundary dates in D2 and E2, use =COUNTIFS(A2:A8,">="&D2,A2:A8,"<="&E2).
The operators make the boundary behavior explicit. >= includes a date equal to the start, while <= includes a date equal to the end. If the intended range should leave out both boundary dates, use strict comparisons instead. With the same cell layout, the exclusive-boundary version is =COUNTIFS(A2:A8,">"&D2,A2:A8,"<"&E2). Be clear about whether the business rule says “between,” “from … through,” or “after … before,” because those phrases can imply different inclusivity.
You can also put fixed dates into a formula with the DATE function, avoiding ambiguity from regional date formats. With dates in A2:A100 and a goal of counting all dates in January 2026, use =COUNTIFS(A2:A100,">="&DATE(2026,1,1),A2:A100,"<"&DATE(2026,2,1)). The first comparison begins on January 1; the second stops before February 1, so all dates in January are included.
Use cells for changing start and end dates
Putting the boundaries in worksheet cells makes a formula easier to reuse. Enter a valid start date in D2 and a valid end date in E2, then change either cell when you need a different reporting period. This is more maintainable than editing date text inside the formula, and it reduces the risk of entering dates in a format Excel interprets differently under another regional setting.
For a straightforward setup, format D2 and E2 as dates and check that the end date is not earlier than the start date. To display them as dates, select the cells and choose Home > Number Format > Short Date. Formatting changes how a value appears, not the underlying value; it does not convert text that merely looks like a date into a usable Excel date.
If a worksheet table grows over time, a table column reference can be more convenient than a fixed range. For example, with an Excel table named Sales and a date column named OrderDate, and the boundaries in D2 and E2, use =COUNTIFS(Sales[OrderDate],">="&D2,Sales[OrderDate],"<="&E2). The structured reference expands as rows are added to the table. The table and column names in a formula must match the names in the workbook.
Count dates in a calendar month
A frequent variation is counting every date in the month that contains a chosen date. If D2 contains any date in the desired month and A2:A100 contains the dates to count, use the first day of that month as the lower boundary and the first day of the next month as an exclusive upper boundary. With that layout, use =COUNTIFS(A2:A100,">="&EOMONTH(D2,-1)+1,A2:A100,"<"&EOMONTH(D2,0)+1).
For example, if D2 is any date in February 2026, the formula starts at February 1 and stops before March 1. It therefore includes February 28 without needing a separate rule for month length or leap years. EOMONTH returns the last day of a month, and adding one produces the next day. The exclusive upper boundary is also useful when the date column may contain times.
If D2 is already guaranteed to contain the first of the month, the formula can be shorter. With dates in A2:A100 and the first of the target month in D2, use =COUNTIFS(A2:A100,">="&D2,A2:A100,"<"&EDATE(D2,1)). EDATE advances the date by one month. Do not use this version if D2 might be, for example, the 15th of the month: it would then count from the 15th through the day before the next month’s 15th, not the whole calendar month.
Handle dates that include times
A cell displayed as 1/15/2026 may contain a time as well as a date, even if the cell format hides the time. A stored value of January 15 at 2:00 p.m. is later than January 15 at midnight. Consequently, a criterion of less than or equal to an end-date cell set to January 15 at midnight can omit records later that same day.
For a range that includes every timestamp on the end date, use an exclusive upper boundary: less than the day after the end date. With timestamps in A2:A100, a start date in D2, and an end date in E2, use =COUNTIFS(A2:A100,">="&D2,A2:A100,"<"&E2+1). If E2 is January 15, the formula includes times throughout January 15 and stops at midnight on January 16. This method also works when the values have no time component.
The same approach works for a month or other period: set the upper bound to the first instant of the following period and use a strict less-than test. This is often safer than trying to construct a final timestamp such as 11:59:59 p.m., because Excel can store time with finer precision than a manually chosen last second.
Common problems and checks
- The result is zero: Check whether the source values are real Excel dates rather than text. Select a source cell and change its number format; if it still behaves like text or does not become a date value, convert or re-enter the data as dates before counting.
- The last day is missing: If the source contains timestamps, replace the inclusive end-date criterion with a less-than criterion for the day after the end date, as shown above.
- The formula returns an unexpected count: Verify that the start and end cells contain the intended dates, and check for blank cells, duplicate records, or dates outside the visible portion of the list. A filter or sort can help inspect the source values.
- The formula shows an error after copying: Check that the criterion ranges have matching dimensions and that cell references point to the intended cells. For a formula copied down, use absolute references for fixed boundaries if needed.
- The date appears correct but the comparison fails: A display format can make text resemble a date. Formatting alone does not repair the stored value. Use Excel’s date conversion tools or replace the source values with valid dates, then recalculate.
In Microsoft 365 for Windows, Excel formulas normally use commas between arguments, as in the examples. Some regional settings use semicolons instead, and date displays vary by region. If Excel rejects a formula copied from a page, check the list separator setting and use the separator expected by your installation. The formula logic is the same in Excel for Mac and Excel for the web; interface details and regional separators can differ.
COUNTIF, COUNTIFS, and other options
Use COUNTIF when there is only one condition. With dates in A2:A100 and a start date in D2, this formula counts all dates on or after the start, with no end-date restriction: =COUNTIF(A2:A100,">="&D2). It is not a complete date-range count by itself. Adding a second independent COUNTIF is not generally equivalent to requiring both conditions; it can count dates outside the intended intersection.
COUNTIFS is the clearest choice for a simple date interval, especially when the conditions apply to the same date column. It can also combine a date range with another condition. For example, if A2:A100 contains dates, B2:B100 contains status values, D2 and E2 hold the date boundaries, and F2 contains the status to match, use =COUNTIFS(A2:A100,">="&D2,A2:A100,"<"&E2+1,B2:B100,F2). This counts rows with the specified status and a date from the start through the end, including timestamps on the end date.
For a one-off review, a table filter can show records within a date range without creating a count formula. A PivotTable can summarize records by date periods when you need repeated reports or grouped totals. Those options change how you inspect or summarize the data; COUNTIFS is usually simpler when the desired output is a single count that should update as the boundaries change.
Frequently Asked Questions
Can COUNTIF count dates between two dates?
Not by itself with two conditions. COUNTIF accepts one criterion; use COUNTIFS to apply both a lower and upper date boundary.
How do I count dates including both the start and end date?
Use a greater-than-or-equal test for the start and a less-than-or-equal test for the end. If the data contains times, use less than the day after the end date instead so late timestamps on that date are included.
How do I count dates in a month in Excel?
Set the lower boundary to the first day of the month and the exclusive upper boundary to the first day of the next month. This includes all dates in the month and avoids hard-coding its final day.
Why does my date-range formula return zero?
The source dates or boundary cells may be stored as text, or the boundaries may not cover the values as expected. Confirm the cells contain real date values and review whether timestamps require an exclusive next-day limit.
Can I use dates typed directly into the formula?
Yes, but date text can be interpreted differently under different regional settings. Referencing date cells is usually easier to maintain; DATE(year,month,day) is another reliable way to build a fixed date in a formula.
Related
- Content Calendar Template Excel: Create and Use One
- Ctrl+G in Excel: Open Go To and Select Cells
- Excel Redo Shortcut: How Ctrl+Y Works and When It Fails
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- DATEDIF in Excel: Formula, Examples, and Common ErrorsThe DATEDIF formula in Excel calculates the time between two dates in days, months, or years: =DATEDIF(start_date,end_date,unit). It…
- BMI Calculation in Excel: Formula, Setup, and TipsTo calculate BMI in Excel, divide weight in kilograms by height in metres squared. If weight is in…
- Calculate Annual ROI in Excel: Formula and ExamplesTo calculate annualized ROI in Excel, divide the ending value by the initial investment, raise the result to…
- Conditional Formatting in Excel: If-Then Color RulesYes. In Excel, select the cells you want to format, then choose Home > Conditional Formatting and create…
- Excel Power Query Left Function: How to Extract TextIn Excel Power Query, the equivalent of the worksheet LEFT function is Text.Start(text, count): it returns the first…
- Filter by Month in Excel: Step-by-StepTo filter Excel dates to one particular month and year, open the date column’s filter menu and choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.