To count dates between two boundaries in Excel, use COUNTIFS with the same date range twice: once for the start and once for the end. With dates in A2:A100, this formula counts dates on or after January 1, 2025, and on or before January 31, 2025: =COUNTIFS(A2:A100,">="&DATE(2025,1,1),A2:A100,"<="&DATE(2025,1,31)). The boundaries are inclusive, so dates on both January 1 and January 31 are counted.
How COUNTIFS date criteria work
COUNTIFS counts rows only when every supplied condition is true. Its arguments come in range-and-criteria pairs: first a range to check, then the criterion to apply to that range. For a date interval, the date range appears in two pairs. The first criterion sets a lower boundary; the second sets an upper boundary. A row must satisfy both.
Excel stores genuine dates as serial numbers and times as fractions of a day. A date criterion is therefore a numeric comparison, even though Excel displays the value in a familiar date format. The comparison operators are written as text, such as ">=" for greater than or equal to. When the boundary comes from a cell or a function such as DATE, join the operator and boundary with the ampersand concatenation operator.
The two criteria are not a special “between” operator. They are separate tests combined with AND logic. That makes the method easy to extend: another range-and-criteria pair can require a matching department, status, customer, or other field at the same time.
Build the basic inclusive formula
For a fixed interval, use >= for the start and <= for the end. In the formula below, the assumed layout is dates in A2:A100; it counts dates from January 1 through January 31, 2025, including both endpoints.
=COUNTIFS(A2:A100,">="&DATE(2025,1,1),A2:A100,"<="&DATE(2025,1,31))
DATE(year,month,day) constructs a real Excel date from numeric components, avoiding ambiguity about whether a typed value such as 1/2/2025 means January 2 or February 1 under a particular regional setting. Excel may display the result using the computer’s date format, but the comparison uses its underlying date value.
For a reusable worksheet, put the start date in D2 and the end date in E2. Assuming dates to count are in A2:A100 and D2 and E2 contain valid Excel dates, use:
=COUNTIFS(A2:A100,">="&D2,A2:A100,"<="&E2)
Now users can change the two boundary cells without editing the formula. Format D2 and E2 as dates so their contents are clear, and check that the start date is not later than the end date.
Worked example with sample records
Suppose A2:A8 contains these seven genuine dates: January 3, January 15, January 31, February 1, February 14, February 28, and March 1, 2025. To count January records, the formula assumes those dates are in A2:A8 and uses January 1 and January 31 as the inclusive boundaries:
=COUNTIFS(A2:A8,">="&DATE(2025,1,1),A2:A8,"<="&DATE(2025,1,31))
The result is 3: January 3, January 15, and January 31 meet both conditions. February 1 is outside the interval, while December 31, 2024, would fail the lower-bound test. The example also illustrates why the end criterion matters: a lower bound by itself would count every later date, not just dates in January.
If each row also has a department in column B, you can count only January records for the North department. Assuming dates are in A2:A8 and department names are in B2:B8, with the text North in D2, use:
=COUNTIFS(A2:A8,">="&DATE(2025,1,1),A2:A8,"<="&DATE(2025,1,31),B2:B8,D2)
All range arguments must cover corresponding rows and have the same dimensions. Here both ranges run from row 2 through row 8, so each date is tested alongside the department on that same row.
Choose inclusive or exclusive boundaries
Inclusive boundaries are common when someone asks for activity “from January 1 to January 31.” Use >= and <= when both named dates should qualify. If the wording or business rule excludes either boundary, change that operator deliberately:
- Use
>to exclude the start date. - Use
<to exclude the end date. - Use
>and<to exclude both boundary values.
For example, an interval written as greater than January 1 but less than January 31 does not include records on either of those dates. “Between” can be ambiguous in ordinary language, so decide whether the endpoints count before choosing operators. For date-only data, inclusive comparisons are often the clearest interpretation.
Do not try to put both comparisons into one criterion string. COUNTIFS expects each condition as a separate range-and-criterion pair, which is why the date range is repeated.
Handle dates that include times
A date-time value contains both a date and a time. A value displayed as January 31 may actually be January 31 at 2:30 p.m. Because that value is greater than the serial value for January 31 at midnight, an inclusive upper condition of less than or equal to the end date can omit it. This is a common reason a formula appears to miss records from the last day.
For date-time data, use the first instant of the start date as an inclusive lower bound, and the first instant of the day after the end date as an exclusive upper bound. Assuming timestamps are in A2:A100 and the requested interval is January 2025, use:
=COUNTIFS(A2:A100,">="&DATE(2025,1,1),A2:A100,"<"&DATE(2025,2,1))
This counts every timestamp from January 1 at midnight up to, but not including, February 1 at midnight. It includes all times on January 31 without needing to guess the final possible time value. For date-only entries, the inclusive end-date formula is also appropriate; the exclusive-next-day pattern is especially robust when times may be present.
Use cells for changing date ranges
Boundary cells make a report easier to maintain. A user can enter a new start and end date, and the count updates automatically. For a month-by-month report, one option is to store the first day of the month in D2 and use the first day of the next month as the exclusive end. Assuming timestamps are in A2:A100 and D2 contains the month’s first date, the formula is:
=COUNTIFS(A2:A100,">="&D2,A2:A100,"<"&DATE(YEAR(D2),MONTH(D2)+1,1))
For a D2 value of January 1, 2025, that formula counts timestamps in January and excludes February 1 onward. The month calculation also handles December: adding one to month 12 rolls the date into January of the next year. This pattern works for date-only records as well as timestamps.
You can combine these date boundaries with other criteria, such as a category in another column. Keep the date range and category range aligned row by row, and add a separate pair for each additional condition. Multiple conditions in COUNTIFS are all required to match; it does not treat them as alternatives.
Common errors and how to fix them
- Dates stored as text: Text that looks like a date may not compare as a date. Check whether the cells are real date values, and convert imported text dates before relying on a date count. Changing the number format alone does not convert text into numeric dates.
- Wrong regional interpretation: A typed date can be interpreted differently depending on system settings. Use unambiguous date entry, or build fixed boundaries with
DATEas shown above. - End-date records missing: Check for time portions in the source values. Use an exclusive boundary at the next day when timestamps are possible.
- Ranges do not match: Every criteria range in the formula should cover the same number of rows and corresponding records. A mismatched range can cause an error or incorrect counting.
- Operators are not joined to cell values: When a criterion uses a cell boundary, combine the operator and the cell reference, for example
">="&D2. A criterion written as justD2tests for equality, not for dates after D2. - Formula separators differ: The examples use commas, as in standard English-language Excel settings. Some regional configurations use semicolons as argument separators. If Excel rejects a correctly structured formula, check the list separator expected by that installation.
To investigate a surprising count, inspect a few source cells in the formula bar, verify the actual start and end values, and test whether timestamps are present. Also check for blank cells: blanks do not normally pass a positive date lower-bound test, but inconsistent or text-formatted data can still make a dataset behave differently from expectations.
COUNTIFS compared with alternatives
COUNTIFS is usually the most direct option when a worksheet needs a count within a date interval and perhaps a few additional conditions. It is available in current desktop Excel and Microsoft 365. Its range-and-criteria structure is readable, and it avoids helper columns for ordinary date filters.
COUNTIF handles just one condition, so it cannot by itself express both a lower and upper date boundary. You could subtract counts in some situations, but that is less clear than writing both tests in COUNTIFS. A PivotTable is useful when you need to summarize many categories by date, inspect the grouped records, or interactively filter a larger report; it may be more setup than needed for one count cell.
For a simple count, avoid building a more complex formula than necessary. Use a PivotTable or a filtered table when the task is broader than counting, such as reviewing which records were included or comparing totals across multiple date periods.
Frequently Asked Questions
Can COUNTIFS count dates between two cells?
Yes. Put the start and end dates in cells and use them as the two criteria boundaries. Join each comparison operator to its cell reference with &; otherwise Excel may interpret the criterion as an equality test.
Does COUNTIFS include the start and end dates?
It does when the formula uses >= for the start and <= for the end. Change either operator to a strict comparison if that endpoint should be excluded. For timestamps, an exclusive next-day upper boundary avoids missing times on the last date.
Why does COUNTIFS return zero for dates that look correct?
The source values or boundary cells may be stored as text rather than real Excel dates, or the formula may use the wrong date interpretation for the computer’s regional settings. Confirm that the values are numeric dates and that the boundary order is correct.
Can I add a department or status condition?
Yes. Add another range-and-criteria pair, such as a department range and a cell containing the department to match. Make sure that added range has the same dimensions and row alignment as the date range.
How do I count one whole month, including times?
Use the first day of the month as an inclusive lower bound and the first day of the next month as an exclusive upper bound. That counts every timestamp in the month without depending on the final time of its last day.
Related
- Excel Data Validation Simple List: Create and Manage It
- Excel Data Validation List: Can It Show Two Columns?
- Excel Data Validation List With a Blank Option
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Import CSV: Formula or the Right Import MethodExcel does not have a built-in worksheet formula that imports a CSV file from your computer. In Microsoft…
- Sort Excel by Color: Steps, Options, and TroubleshootingTo sort an Excel sheet by color in Microsoft 365 for Windows, select a cell in your data,…
- Excel Formula to Extract Unique Values from a ListIn Excel for Microsoft 365, enter =UNIQUE(A2:A20) to extract the distinct values from a list in cells A2:A20.…
- Excel Filter Table by Keyword: Steps, Options, and TipsTo filter an Excel table by keyword, open the filter arrow in the column you want to search,…
- COUNTIF Excel: Count Dates by Day, Month, or RangeYes. Excel’s COUNTIF function can count dates that match a specific day or a condition such as before…
- Excel Split Cell After First Space: Formulas and StepsTo split text in Excel after its first space, use =TEXTBEFORE(A2," ") for the text before the space…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.