Excel COUNTIF for Dates Between a Range

Formulas & functions · Updated November 19, 2026

To count dates between two boundaries in Excel, use COUNTIFS, which applies both a start-date and an end-date condition: with dates in A2:A100 and the boundary dates in D2 and E2, enter =COUNTIFS(A2:A100,">="&D2,A2:A100,"<="&E2). This counts dates on both boundary days; Excel’s COUNTIF handles only one condition at a time.

Why COUNTIFS is the right function

The phrase “COUNTIF dates between a range” often refers to counting dates that fall within a start and end date. The key detail is that “between” requires two tests: the value must be on or after the start and on or before the end. COUNTIFS is designed to apply multiple conditions, so it is the straightforward choice.

COUNTIF accepts one range and one criterion. It can count dates before a cutoff or after a cutoff, but it cannot independently test both ends of a range in one call. You could build more complicated workarounds with multiple functions, but they are less clear than using COUNTIFS for this two-boundary task.

Excel stores genuine dates as serial numbers and compares those underlying values. The date’s display format—such as 3/14/2026 or 14-Mar-2026—does not change the comparison. The cells must contain actual Excel dates, however; text that merely looks like a date may not behave as expected.

Set up the date range formula

Put the dates you want to count in one column, with no header inside the range being counted. Enter the earliest boundary date in one cell and the latest boundary date in another. For example, use D2 for the start and E2 for the end. With the dates in A2:A100 and those boundary cells in D2 and E2, use:

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

The criteria combine a comparison operator with a cell reference. The ampersand joins the text operator, such as >=, to the date value in D2. The second condition does the same with <= and E2. In COUNTIFS, each condition is supplied as a range followed by a criterion, so the same date range appears twice.

For a dependable setup:

  • Make sure the start date is earlier than or equal to the end date.
  • Use the same-sized range for both conditions. Here, both are A2:A100.
  • Store the boundaries in cells as real dates, not text strings.
  • Format the result cell as General or Number if you want to see the count clearly.

Worked example with inclusive boundaries

Suppose A2:A8 contains January 5, January 10, January 15, January 20, January 25, February 1, and a blank cell, in that order. Put January 10 in D2 and January 20 in E2. With dates in A2:A8 and those boundaries in D2 and E2, enter:

=COUNTIFS(A2:A8,">="&D2,A2:A8,"<="&E2)

The result is 3: January 10, January 15, and January 20 meet both conditions. The endpoints are included because the operators are >= and <=. January 5 and January 25 fall outside the interval, February 1 is later than the end date, and the blank contributes no matching date.

Using cell references for the boundaries makes the formula reusable. Change D2 or E2, and the count updates without editing the formula. For a report that needs several periods, copy the formula alongside different start and end dates, while keeping the date-data range fixed if appropriate.

Choose inclusive or exclusive endpoints

“Between” can mean different things in different reports. Many date counts should include both the first and last day, which is why the inclusive formula uses greater-than-or-equal and less-than-or-equal operators. If the requirements say to exclude both boundary dates, use strict comparisons instead. With dates in A2:A100 and the boundaries in D2 and E2, the exclusive version is:

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

This counts values strictly later than D2 and strictly earlier than E2. You can also mix the operators when only one endpoint is included—for example, include the start day but exclude the end day with >= and <. Decide what the boundaries mean before choosing operators; changing them can alter the result by one or more records.

Another useful convention is a half-open interval: include the start and exclude the next period’s start. That convention is especially helpful for consecutive date or time periods because it avoids counting a shared boundary twice. For day-level dates, the condition would be on or after the first date and before the date following the final included day.

Count dates that also contain times

A date-time value includes a fractional part for the time. If E2 contains a date at midnight, the inclusive formula with <=E2 will not count a value later on that same calendar day, such as 3:00 p.m.; that value is greater than midnight. This is a common reason a formula appears to miss records on its end date.

To include every time on the end date, use a less-than condition for the following day. With date-time values in A2:A100, the start date in D2, and the final calendar date in E2, use:

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

If E2 is April 30, the upper limit becomes May 1 at midnight, and the strict less-than condition includes April 30 values up to but not including that next midnight. This approach also works when the source has a mix of midnight dates and date-times. It assumes E2 is a date without a meaningful time portion; if it already contains a specific end time, use the actual required timestamp as the boundary instead.

Enter and verify real Excel dates

Excel normally recognizes dates entered in a form supported by your regional settings. If a date is ambiguous—for example, 4/5/2026 could mean April 5 or May 4 in different date conventions—confirm how Excel interpreted it. Select the cell and inspect the formula bar, or temporarily apply a clear date format such as a day-month name format. Changing a cell’s number format changes its appearance, not the underlying value.

For unambiguous date boundaries created in a formula, you can use Excel’s DATE function. With the counted dates in A2:A100 and a start date of March 1, 2026 and end date of March 31, 2026, enter:

=COUNTIFS(A2:A100,">="&DATE(2026,3,1),A2:A100,"<="&DATE(2026,3,31))

DATE takes year, month, and day arguments and returns a date value, avoiding ambiguity from typing a date string directly into a formula. The formula uses the same inclusive logic as the cell-reference version. In Excel configured for a locale that uses semicolons as argument separators, Excel may display or require semicolons instead of commas; the formulas here use the standard comma syntax.

Troubleshoot counts that look wrong

If the result is zero or lower than expected, check the stored values and the formula ranges before changing the criteria. A date-looking text value may not compare like a real date. A quick visual check is to select a source cell and change its number format; if it behaves like text, formatting alone may not convert it. Use Excel’s data-conversion tools or replace the source with genuine date values, then recalculate.

Also check for the following issues:

  • Reversed boundaries: If D2 is later than E2, no value can satisfy both ordinary inclusive conditions. Correct the inputs or add a separate check to flag reversed dates.
  • Different range sizes: The criteria ranges in a COUNTIFS formula should have matching dimensions. Keep both references aligned, for example A2:A100 rather than pairing it with A2:A99.
  • Hidden times: A displayed date may conceal a time value. Use the next-day exclusive upper limit when the entire final calendar day should count.
  • Wrong data column: Confirm that the range contains the dates being evaluated, not a neighboring column or a header.
  • Calculation or edit mistakes: Check that the formula refers to the intended boundary cells and that the workbook has recalculated after changes.

Blank cells are generally not valid dates to include in a date interval. If a data column contains errors or unusual text alongside dates, clean or review those entries rather than assuming every cell represents a date. For an auditable report, keep the original data intact and add a separate cleaned date column if conversion is necessary.

Count dates by month or year

The same two-boundary pattern can count a calendar month or year. For example, a month’s start is included and the first day of the next month is excluded. This is useful when the source contains times because no value within the final day slips past a midnight end boundary. With date-time values in A2:A100, count all records in March 2026 using:

=COUNTIFS(A2:A100,">="&DATE(2026,3,1),A2:A100,"<"&DATE(2026,4,1))

This counts values from March 1 at midnight up to, but not including, April 1 at midnight. It therefore includes March 31 records with times. To count a different month, update the two boundary dates. For a year, use January 1 of that year as the start and January 1 of the following year as the exclusive end.

A fixed date formula is suitable for a one-off calculation; cell references are easier to maintain in a recurring report. If you need a count by month for many months, put each month’s start and next-month boundary in cells and copy the formula down. That keeps the date logic visible and reduces the chance of accidentally omitting a day.

Frequently Asked Questions

Can COUNTIF count dates between two dates?
Not directly with one criterion: COUNTIF tests one condition. Use COUNTIFS to test both the lower and upper date boundaries in one formula.

Does the formula include the start and end dates?
Yes, when it uses >= for the start and <= for the end. Replace an operator with > or < to exclude that boundary.

Why does the formula miss entries on the end date?
The source may contain times, while the end-date cell represents midnight at the beginning of that day. Use a less-than condition for the day after the end date to include all times on the final day.

Can I use typed dates instead of boundary cells?
Yes, but date interpretation can depend on regional settings. Using date cells or DATE(year,month,day) makes the intended boundaries clearer and easier to revise.

Does this formula work in Excel for Mac or Excel for the web?
COUNTIFS and the date criteria shown here work in current Excel for Microsoft 365 on Windows, Mac, and the web. The formula logic is the same; regional settings may affect whether your Excel uses commas or semicolons between arguments.

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.