Excel Formula for Date Range: Count Dates and Match

Formulas & functions · Updated November 18, 2026

For dates in A2:A100 and a start date in D2 and end date in E2, count dates in the inclusive range with =COUNTIFS(A2:A100,">="&D2,A2:A100,"<="&E2). The same date-boundary idea works for summing values, checking whether one date qualifies, filtering matching records, and highlighting dates.

The basic date-range formula

A date range has two boundaries: a start and an end. In Excel, a practical setup is to keep the dates you want to test in one column and put the start and end dates in separate cells. In the example above, D2 is the first date to include and E2 is the last date to include. COUNTIFS applies both conditions to the date column, so a date must be on or after D2 and on or before E2 to count.

For a concrete example, suppose A2:A7 contains January 3, 10, 15, 20, and 31, 2025, plus February 2, 2025. If D2 is January 10 and E2 is January 20, the formula returns 3: the January 10, 15, and 20 entries. Both endpoints are included. This is usually what people mean by “between these dates” when they are counting calendar dates.

COUNTIFS is useful because it evaluates multiple criteria at once. Its date conditions are written as text operators joined to cell references with the ampersand. Keep the operators inside quotation marks and the cell references outside them. The date cells themselves should contain real Excel dates, not text that merely looks like a date.

How Excel dates and boundaries work

Excel stores dates as serial numbers, which lets it compare and calculate them. A displayed date such as January 10, 2025 is a formatted value; the cell’s number format controls how that underlying value appears. This is why date comparisons can work even if the cells display dates in different regional styles, provided Excel recognizes each value as a date.

Inclusive boundaries use “greater than or equal to” for the start and “less than or equal to” for the end. If your definition excludes the endpoints, use strict greater-than and less-than operators instead. The choice is a business rule, not a special Excel setting: a reporting period covering January 1 through January 31 normally includes both January 1 and January 31.

There is an important complication when cells contain both dates and times. A date with no visible time is treated as midnight at the start of that day. If E2 is January 20, the condition less than or equal to E2 can exclude a timestamp such as January 20 at 3:00 PM, because that value is later than midnight. To include every time on the end date, use an exclusive boundary at the following day.

With date-time values in A2:A100 and date-only start and end values in D2 and E2, this version includes all timestamps on the end date:

=COUNTIFS(A2:A100,">="&D2,A2:A100,"<"&E2+1)

For a start of January 10 and end of January 20, it counts values from midnight on January 10 up to, but not including, midnight on January 21. This pattern is also useful for transaction logs where the time is stored even if the worksheet displays only the date.

Count rows between two dates

Use COUNTIFS when the question is how many records fall in a date range. The ranges supplied for each condition should cover the same rows. For instance, using A2:A100 for one condition and A2:A99 for the other can produce an error because the criteria ranges do not match in size.

If users may enter the start and end dates in the wrong order, you can show a warning instead of silently returning zero. With dates in A2:A100, the start date in D2, and the end date in E2, use:

=IF(D2>E2,"Check start and end dates",COUNTIFS(A2:A100,">="&D2,A2:A100,"<="&E2))

For this formula, D2 later than E2 produces the message “Check start and end dates”; otherwise, it performs the count. This check is especially helpful in a template used by other people. You can also prevent reversed dates at entry time with Data Validation, but a formula-level check keeps the result area informative even if the input values change.

If the date range is fixed, such as a calendar year, you can enter the first and last dates directly into dedicated cells rather than embedding dates in the formula. Keeping boundaries in cells makes the worksheet easier to update for another month or year and reduces the chance that someone edits one criterion but forgets the other.

Sum or average values in a date range

When each date has a related amount, COUNTIFS can count the records but SUMIFS can total the matching amounts. Assume transaction dates are in A2:A7, amounts are in B2:B7, the start is in D2, and the end is in E2:

=SUMIFS(B2:B7,A2:A7,">="&D2,A2:A7,"<="&E2)

For example, if the amounts beside January 10, 15, and 20 are 80, 50, and 200, and those are the three dates inside the chosen range, the result is 330. SUMIFS takes the values to add first, followed by each criteria range and condition. The date range is therefore applied to the date column while the matching amounts come from the amount column.

To average values for matching dates, use AVERAGEIFS. With dates in A2:A7, amounts in B2:B7, and the same start and end cells D2 and E2, the formula is:

=AVERAGEIFS(B2:B7,A2:A7,">="&D2,A2:A7,"<="&E2)

For amounts of 80, 50, and 200 on the three qualifying dates, the result is 110. AVERAGEIFS averages the matching numeric values; it does not treat empty amount cells as zero. If there are no matching numeric values, Excel returns a divide-by-zero error, so a report may need an IFERROR wrapper or a separate check for whether any records match.

Return matching records or generate a date list

If you want to see the records rather than only a count or total, FILTER can return matching rows as a spilling array. FILTER is available in Microsoft 365 and Excel 2021 or later. Assume dates are in A2:A100, descriptions are in B2:B100, and the range boundaries are in D2 and E2:

=FILTER(A2:B100,(A2:A100>=D2)*(A2:A100<=E2),"No matches")

The two comparisons create true-or-false results, and multiplying them requires both conditions to be true. The formula returns the date and description columns for qualifying rows, or the text “No matches” if none qualify. Enter it in an area with room for results to spill; cells in the spill area must be clear. In Excel for the web, the formula works in supported versions with dynamic arrays. Older Excel versions without FILTER can use the AutoFilter feature or a helper column instead.

A different interpretation of “date range” is a list of every calendar date between two endpoints. With a start date in D2 and end date in E2, Excel 2021 or Microsoft 365 can generate that list with:

=SEQUENCE(E2-D2+1,1,D2,1)

If D2 is January 10, 2025 and E2 is January 20, 2025, the formula spills 11 consecutive date values. Format the result cells as dates if they display as serial numbers. The plus one makes both endpoints part of the list. This formula expects the start date to be on or before the end date; it will not create a useful descending list if the dates are reversed.

Highlight dates that fall inside a range

Conditional formatting can visually mark qualifying dates without adding a result column. For dates in A2:A100 and boundaries in D2 and E2, select A2:A100, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter:

=AND($A2>=$D$2,$A2<=$E$2)

Choose a format and confirm the rule. The dollar signs lock the boundary cells and the date column, while the row number remains relative so Excel checks each row in the selected range. If you want to highlight an entire record, select the full data area, such as A2:B100, before creating the rule and keep the formula’s date-column reference aligned with the first row.

On Excel for Mac, the Conditional Formatting command is also on the Home tab, although the dialog layout can differ slightly from Windows. In Excel for the web, conditional formatting is available from the Home tab as well. The formula uses the same relative-reference principle across these versions.

Common problems and practical fixes

  • Dates are stored as text. A value that looks like a date may not compare as a date. Test a questionable cell by changing its number format to General; a recognized date typically displays as a serial number. Convert text dates to real dates before relying on criteria formulas. Text-to-Columns or DATEVALUE may help, depending on the input format and regional settings.
  • The end date misses afternoon records. If the source includes times, use a less-than comparison against the day after the end date, as shown above. A simple “less than or equal to end date” condition only includes the exact date value at midnight, not later timestamps.
  • The formula returns zero unexpectedly. Check that D2 and E2 are valid date values, that the start is not later than the end, and that the criteria ranges cover the intended rows. Also check for text dates, hidden time components, and dates outside the selected period.
  • Formula separators differ. The formulas here use commas, as required by many English-language Excel settings. Some regional settings use semicolons as argument separators. If Excel rejects a formula despite correct logic, your system’s list-separator setting may require that regional form.
  • The result displays as a number. A date result can appear as a serial number when the cell is formatted as General or Number. Apply a date format using Home > Number Format, or use a custom date format that suits the worksheet.

For predictable results, keep input dates and source dates consistent, use clear labels for the start and end cells, and decide whether the period is inclusive before building the formula. When data is imported from another system, verify a few sample values rather than assuming that a date-like display guarantees a true date value.

Choosing the right formula

The right formula depends on what the result should represent. COUNTIFS answers “how many rows?”; SUMIFS answers “what is the total?”; AVERAGEIFS answers “what is the average?”; FILTER returns the matching records; and conditional formatting changes their appearance. These functions use the same basic start and end logic, so once the boundary rule is clear, it is straightforward to adapt the method.

For a simple list of dates, the inclusive COUNTIFS formula is usually the clearest starting point. For sales or expense data, use SUMIFS with the date column as the criteria range and the amount column as the sum range. For event logs with timestamps, prefer an end-exclusive next-day boundary. For a report that needs a visible list of matching entries, choose FILTER if the Excel version supports it.

Excel for Microsoft 365 on Windows supports the formulas shown here. FILTER and SEQUENCE are also available in Excel 2021 and later, but are not available in some older perpetual versions. The core COUNTIFS, SUMIFS, AVERAGEIFS, IF, and AND functions are broadly supported in modern Excel versions. Mac and web users can use the same formulas, though ribbon layout and some dialog details may differ.

Frequently Asked Questions

How do I count dates between two dates in Excel?
With dates in A2:A100, the start in D2, and the end in E2, use =COUNTIFS(A2:A100,">="&D2,A2:A100,"<="&E2). It counts both boundary dates.

Does COUNTIFS include the start and end dates?
Yes, when the criteria use greater than or equal to for the start and less than or equal to for the end. Replace those operators with strict greater-than and less-than if your range should exclude both endpoints.

How do I include the entire end date when my cells contain times?
Use a condition that is less than the day after the end date. That includes every time on the selected end date while excluding midnight at the start of the following day.

How can I sum values within a date range?
Use SUMIFS with the amounts as its sum range and the date column as the criteria range. Add one criterion for the start boundary and one for the end boundary.

Why does my date-range formula return zero?
Common causes are dates stored as text, a reversed start and end date, mismatched criteria ranges, or timestamps that fall outside a midnight end boundary. Confirm the cell values are real dates and choose the correct inclusive or timestamp-safe comparison.

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.