Excel COUNTIF: Count Dates Between Two Dates

Formulas & functions · Updated November 19, 2026

To count dates in a column that fall between a start date and an end date, use COUNTIFS, which applies both date limits: =COUNTIFS(A2:A100,">="&D2,A2:A100,"<="&E2). Here, dates are in A2:A100, the start date is in D2, and the end date is in E2; the formula counts both boundary dates.

Why COUNTIFS is usually the right function

Excel’s COUNTIF function tests a range against one condition. A date interval needs two conditions on the same range: the date must be on or after the start, and on or before the end. COUNTIFS is designed to apply multiple criteria, so it is usually the clearest way to count dates between two limits.

Although people often search for “COUNTIF between two dates,” using COUNTIF twice is another valid approach. The two-function method can be useful when you are learning how the boundaries work, but COUNTIFS expresses the interval more directly and is easier to expand if you later add more conditions, such as a category or salesperson.

In Microsoft Excel for Microsoft 365 on Windows, enter the formula in the cell where you want the count, then press Enter. The formula works the same way in Excel for Mac and Excel for the web; regional settings may require semicolons instead of commas between arguments. The examples here use commas, as in standard English-language Excel formulas.

How the two date criteria work

In the formula, A2:A100 is checked twice. The first criterion, ">="&D2, means “greater than or equal to the date in D2.” The second, "<="&E2, means “less than or equal to the date in E2.” The ampersand joins each comparison operator to the date value in its cell, creating a criterion Excel can evaluate.

This is different from putting a range of dates into one criterion. A criterion such as “between D2 and E2” is not a built-in single condition for COUNTIF. Instead, use two criteria with COUNTIFS. The criteria range must be the same size as the counted range; using A2:A100 for both conditions meets that requirement.

The usual inclusive interval counts the start and end dates if they appear in the data. If the start and end cells are real Excel dates, the formula compares their underlying numeric date values, not just the way they look on screen. Formatting a cell to display a date does not, by itself, turn text into a date value.

A worked example with a small date list

Suppose A2:A8 contains January 3, January 10, January 15, January 20, January 25, February 1, and one blank cell, all in 2025. D2 contains January 10, 2025, and E2 contains January 20, 2025. With those cells as the layout, enter =COUNTIFS(A2:A8,">="&D2,A2:A8,"<="&E2). The result is 3, because January 10, January 15, and January 20 meet both criteria.

To check the count manually, first identify dates on or after January 10, then remove any dates after January 20. January 25 and February 1 fail the upper limit; January 3 fails the lower limit; and the blank does not qualify as a date in the interval. This small check is helpful when validating a formula against a larger worksheet.

Keep the criteria range and the counted range aligned. For example, if your records start on row 2, use A2:A100 for both criteria rather than mixing A:A with A2:A100. Whole-column references can also work, but matched, bounded ranges make the formula easier to inspect and can be more efficient in large workbooks.

Inclusive dates versus exclusive boundaries

“Between” can mean different things in different contexts. The common reporting interpretation is inclusive: include records on the starting date and on the ending date. That is what the >= and <= operators do. If a rule requires dates strictly inside the interval, use > for the start and < for the end.

With dates in A2:A8 and boundary dates in D2 and E2, this formula counts only dates strictly after D2 and strictly before E2: =COUNTIFS(A2:A8,">"&D2,A2:A8,"<"&E2). Using the example dates above, the result is 1, because only January 15 is strictly inside the interval. Choose the operators to match the rule you are reporting, rather than assuming “between” always includes both endpoints.

A related alternative uses two COUNTIF calculations. With the same A2:A8, D2, and E2 layout, =COUNTIF(A2:A8,">="&D2)-COUNTIF(A2:A8,">"&E2) returns 3. It counts dates from the start onward, then subtracts dates later than the end. This works for the inclusive example, but COUNTIFS is generally easier to read because both limits are visible together.

When the date column also contains times

Excel can store a date and time in one numeric value. A cell displayed as January 20 may actually contain January 20 at 2:30 p.m. If your upper criterion is “less than or equal to January 20 at midnight,” that afternoon entry is later than the criterion and will not be counted. This is a frequent reason a count appears one or more records too low.

For a whole-calendar-day interval that includes every time on the ending date, use a strict upper limit of the next day. With timestamps in A2:A5, a start date in D2, and an end date in E2, enter =COUNTIFS(A2:A5,">="&D2,A2:A5,"<"&E2+1). If the four cells contain January 20 at midnight, January 20 at 2:30 p.m., January 21 at midnight, and January 19 at 11:59 p.m., and D2 and E2 are January 10 and January 20, the result is 2. Both January 20 timestamps are counted, while January 21 at midnight is excluded.

This next-day approach assumes E2 is a date whose time is midnight and that you intend to include the entire calendar day. If E2 itself contains a meaningful time, adding 1 changes the boundary by a full day; use the actual time limits instead. For example, a reporting window ending at 5 p.m. should have an end-date-and-time value that reflects 5 p.m., with a criterion suited to whether that exact instant is included.

Check that Excel recognizes the values as dates

Dates that look alike may be stored differently. Excel stores genuine dates as serial numbers, while imported or manually entered date-looking text remains text. Date criteria may not compare text values as expected. A quick clue is alignment: dates commonly align right by default, while text commonly aligns left, though custom formatting can make alignment an unreliable test.

If the values came from a CSV or another system, inspect a few cells and confirm they behave like dates. You can change the number format to a date format, but formatting alone only changes appearance; it does not convert text to a numeric date. Depending on the source and format, converting the imported column to dates or re-importing it with the correct date interpretation may be necessary. Also check that the start date is not later than the end date, and that neither boundary cell is blank or accidentally text.

  • Confirm the date column contains actual Excel date values, not a mixture of dates and text.
  • Check that the start and end cells contain the intended dates and are in the correct order.
  • Make sure each criterion range covers the same rows as the other criterion range.
  • Look for timestamps if records on the final day seem to be missing.
  • Check whether the report should include or exclude records exactly on either boundary.

Adding another condition to the date count

COUNTIFS can combine the date interval with other criteria. For instance, you might count only transactions in a date window that belong to the category “Travel.” Add a third range and criterion pair, keeping all ranges the same size. This makes COUNTIFS more suitable than trying to nest several single-condition counts as the worksheet grows.

With dates in A2:A100, categories in B2:B100, the start date in D2, the end date in E2, and the category name Travel in F2, use =COUNTIFS(A2:A100,">="&D2,A2:A100,"<="&E2,B2:B100,F2). The result depends on the actual rows, but it counts only entries whose date falls within the inclusive interval and whose category matches F2. If the date column contains times and the end date should include the full day, change the upper date criterion to "<"&E2+1.

For a category condition, exact text matching is usually the clearest choice. If labels contain extra spaces or inconsistent spelling, fix or standardize the source values; a correct date formula cannot compensate for mismatched category text. If the workbook needs several types of date reporting, consider placing start and end dates in clearly labeled cells so users can update the period without editing the formula itself.

Frequently Asked Questions

Can I use COUNTIF to count dates between two dates?
Not with one ordinary COUNTIF criterion, because the interval has a lower and an upper limit. Use COUNTIFS for both conditions, or use the two-COUNTIF subtraction method when you have a reason to express the count that way.

Does the formula include the start and end dates?
Yes, if it uses >= for the start and <= for the end. Replace those with > and < if the endpoints must be excluded. For timestamps and a full final calendar day, use a less-than comparison against the day after the end date.

Why is my count zero when the dates are visible?
The visible values may be text rather than real Excel dates, or the boundary cells may be text, reversed, or blank. Check the values’ underlying type and verify the ranges and dates. Imported date columns are a common source of text-date problems.

Will COUNTIFS count dates that include a time?
Yes, when the stored date-and-time value meets both criteria. However, an end criterion of less than or equal to a date at midnight excludes later times on that day. Use a less-than comparison to the next day when the entire ending date should count.

Can I count dates between two fixed dates without using cells for the limits?
Yes, but putting the boundaries in cells is usually easier to maintain and reduces the chance of mistyping a date inside a formula. Excel date literals in criteria can be sensitive to regional date conventions, so cell references containing valid date values are a dependable approach for reusable worksheets.

Does the formula work in Excel for Mac or Excel for the web?
Yes. COUNTIFS and the comparison criteria work in current Excel for Microsoft 365 on Windows, Mac, and the web. If your regional settings use semicolons as argument separators, replace the formula’s commas with semicolons.

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.