Excel COUNTIF Between Two Dates: Formula and Examples

Formulas & functions · Updated November 19, 2026

To count dates in Excel between two boundary dates, use COUNTIFS, which applies both a start-date and an end-date condition to the same range. With dates in A2:A100, the start date in D2, and the end date in E2, use =COUNTIFS(A2:A100,">="&D2,A2:A100,"<="&E2) for an inclusive range that counts both boundary dates.

The formula and what it counts

Although people often search for “COUNTIF between two dates,” a range with a lower and upper limit needs two conditions. Excel’s COUNTIF function accepts one condition; COUNTIFS accepts multiple conditions and is the straightforward choice for this task. The formula checks each cell in the date range twice: once to see whether it is on or after the start, and once to see whether it is on or before the end.

For the formula above, the assumptions are that A2:A100 contains Excel date values, D2 contains the starting date, and E2 contains the ending date. Its criteria mean “greater than or equal to D2” and “less than or equal to E2.” Because the boundaries are inclusive, a record on either boundary is counted. If the start and end cells contain January 1 and January 31, for example, dates on both January 1 and January 31 qualify.

The ampersand joins each comparison operator, such as >=, to the date value in its cell. The quotation marks around the operator are required in a criteria string. Keep the criteria ranges the same size and shape: here both point to A2:A100. In a real sheet, replace the sample references with the cells used in your data.

How Excel stores dates

Excel stores a genuine date as a number representing a day in its date system, and can display that number in a date format such as 3/15/2026 or 15-Mar-2026. COUNTIFS compares the underlying values, not the way the cells look on screen. This is why a correctly stored date can be displayed in different regional formats and still be compared successfully.

A value that merely looks like a date may instead be text. Text dates can be produced by importing a file, pasting values, or using a format that Excel did not recognize. A number format alone does not convert text into a date. If a date cell is left-aligned unexpectedly, or changing its number format does not change its appearance, check whether it is stored as text. Re-entering the value as a date or converting the imported column may be necessary before the count works reliably.

For predictable results, enter the boundaries as dates in worksheet cells rather than building date strings into the formula. This avoids ambiguity between regional conventions: for example, a string such as 04/05/2026 can mean April 5 or May 4 depending on settings. A cell containing a recognized date avoids that particular interpretation problem.

Set up the date range step by step

  1. Put one date per row in a single column, such as A2:A100. Use real Excel date values, not labels or text descriptions.
  2. Enter the beginning of the period in D2 and its end in E2. Format those cells as dates if needed, using Home > Number Format.
  3. Choose a result cell outside the source data and enter the COUNTIFS formula with the same date range used for both criteria.
  4. Check that the start date is not later than the end date. If it is, the formula will ordinarily return zero because no value can satisfy both conditions.
  5. Test the result against a small sample of records, especially if the source contains imported data or times as well as dates.

For example, assume A2:A8 contains January 5, January 10, January 15, January 20, January 25, February 1, and February 3 of the same year. D2 contains January 10 and E2 contains January 25. In this layout, the formula is =COUNTIFS(A2:A8,">="&D2,A2:A8,"<="&E2). It returns 4, counting January 10, 15, 20, and 25. January 5 falls before the period, while February 1 and 3 fall after it.

Inclusive dates, exclusive dates, and timestamps

For date-only data, inclusive boundaries are usually what readers mean by “between these dates.” The greater-than-or-equal and less-than-or-equal operators include a value that exactly matches either boundary. If your business rule excludes the endpoints, use strict comparisons instead: greater than the start and less than the end. State the boundary rule clearly, because “between” is not always used consistently in reports.

A subtle issue arises when cells contain date and time values. Excel represents a time as a fractional part of a day. A cell displayed as May 5 may therefore contain May 5 at 8:30 a.m., not exactly midnight. An inclusive test against an end date stored as midnight will count May 5 at 12:00 a.m. but miss later times on May 5.

For timestamps, a dependable pattern is to include the start date and exclude the day after the end date. With timestamps in A2:A100, the start date in D2, and the final calendar date in E2, use =COUNTIFS(A2:A100,">="&D2,A2:A100,"<"&E2+1). The exclusive upper limit includes every time on E2 but stops before midnight on the following day. For instance, if the period is May 3 through May 5, a May 5 value at 5:30 p.m. is counted, while a May 6 value is not. This approach assumes D2 and E2 are date boundaries, rather than arbitrary times that must themselves be treated as exact limits.

Common problems and how to fix them

  • The result is zero when dates appear to match. Check whether the source values or boundary cells are text. Confirm the year as well as the displayed month and day, and make sure the criteria range points to the intended column.
  • The result is one too small. If the data includes times, the inclusive end-date test may only include midnight at the start of the final day. Use the exclusive next-day limit described above.
  • The formula returns an unexpected count. Look for blank cells, duplicate records, hidden rows, or values outside the expected period. COUNTIFS counts matching cells whether their rows are visible or filtered out; filtering does not make it a visible-rows-only count.
  • The formula shows an error. Ensure each range has matching dimensions and that commas separate arguments in the formula as shown for standard English-language Excel syntax. Some regional Excel settings use semicolons as argument separators, so the separator may need to match the installation.
  • The count changes after copying the formula. If the boundary cells move when you copy the formula, lock them with absolute references. With dates in A2:A100 and boundary dates in D2 and E2, the copy-ready version is =COUNTIFS(A2:A100,">="&$D$2,A2:A100,"<="&$E$2). The dollar signs keep D2 and E2 fixed. In the HTML formula above, the ampersands before the cell references are part of the Excel syntax.

Also check for dates outside Excel’s supported date range or values containing leading or trailing spaces if a column was imported as text. Applying a date display format may help you inspect genuine dates, but it does not repair inconsistent source values by itself.

Choosing the right variation

Pick the criteria to match the question, not simply the wording of the formula. Use inclusive start and end dates for a normal calendar-date window. Use a strict comparison when a boundary should be excluded. For timestamp logs, use a greater-than-or-equal start and a less-than next-day end so the final calendar date is fully included.

If the start and end dates are typed directly into a formula, Excel date construction can avoid regional date-string ambiguity. With dates in A2:A100 and a period of January 10 through January 25, the formula is =COUNTIFS(A2:A100,">="&DATE(2026,1,10),A2:A100,"<="&DATE(2026,1,25)). It returns 4 for the seven-date example above when those dates are in 2026. Using cell references is usually easier to maintain because a reader can change the period without editing the formula.

For a recurring report, put the date limits in clearly labeled cells and use a result formula next to them. You can then change the reporting period while keeping the calculation intact. If the range will grow, an Excel Table can make the source data easier to expand and reference, but the essential logic remains two criteria applied to the date column.

COUNTIF versus COUNTIFS and other options

COUNTIF is useful when the question has only one date condition, such as counting dates after a cutoff. It does not directly express both a minimum and maximum in one function call. One workaround is to subtract counts from separate COUNTIF calculations, but that is harder to read and easier to get wrong around inclusive boundaries. COUNTIFS communicates the two limits together and can also add conditions for other columns.

For example, you might count orders in a date interval only when their status is “Paid.” That requires a third criterion range for the status column in addition to the two date conditions. When the task is to return the matching records rather than count them, a filtering tool or a dynamic array approach may be more suitable; a count formula returns only a number. For a simple count between two date limits, COUNTIFS remains the clearest fit.

Frequently Asked Questions

Can I use COUNTIF for a date range?
Not by itself for both a start and an end condition. COUNTIF handles one criterion, while COUNTIFS can apply both boundaries to the same date range. Use COUNTIFS for an inclusive date interval.

Does COUNTIFS include the start and end dates?
Yes, when the criteria use greater than or equal to the start and less than or equal to the end. If you use strict greater-than or less-than operators, the corresponding boundary is excluded.

Why does my formula miss records on the final date?
The source may contain timestamps, while the end-date cell represents midnight at the start of that date. Use a less-than comparison against the day after the final date to include all times on the final date.

Can I count dates between two dates in different cells?
Yes. Put the start and end dates in separate cells and join each comparison operator to its cell reference in the COUNTIFS criteria. This makes it easy to update the range without changing the formula.

Does COUNTIFS count dates stored as text?
It may not compare text dates as expected. Excel date criteria work most reliably when both the data and boundaries are genuine date values. Convert imported text dates to real dates before relying on the result.

Will filtering the list change the count?
No. COUNTIFS evaluates matching cells in its specified ranges, including rows hidden by a filter. If you need a count that responds only to visible rows, use a different approach designed for filtered data.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.