COUNTIF Date Range in Excel: Count Dates Between Two Days

Formulas & functions · Updated November 22, 2026

To count dates between two limits in Excel, use COUNTIFS with a start-date condition and an end-date condition. For example, =COUNTIFS(B2:B100,">="&DATE(2025,1,1),B2:B100,"<"&DATE(2025,2,1)) counts dates from January 1 through January 31, 2025, when the cells contain real Excel dates.

Why a date range needs COUNTIFS

COUNTIF checks one condition, such as whether a date is on or after a particular day. A range has two boundaries: a lower limit and an upper limit. COUNTIFS is designed to apply multiple conditions at once, so it can count dates that meet both boundaries. Despite the common search phrase “COUNTIF date range,” COUNTIFS is usually the right function for counting dates between two days.

In Microsoft Excel for Microsoft 365 on Windows, the basic pattern is to use the same date column twice: once for dates on or after the start, and once for dates before the day after the end. The conditions are combined, so a row is counted only when both are true. The formula works with date values regardless of how the cells are formatted to display them, provided Excel recognizes them as dates.

The basic formula and how it works

Suppose transaction dates are in B2:B100. To count dates in January 2025, use this formula. The range ends at February 1 exclusively, which includes all dates in January:

With dates in B2:B100, this formula returns the number of entries dated January 1–31, 2025:

=COUNTIFS(B2:B100,">="&DATE(2025,1,1),B2:B100,"<"&DATE(2025,2,1))

The first condition means “greater than or equal to January 1.” The second means “less than February 1.” The comparison symbols are written inside quotation marks because Excel treats each condition as criteria text. The ampersand joins that text to a date value returned by DATE. Using DATE(year,month,day) avoids relying on whether a particular computer interprets a typed date as month/day/year or day/month/year.

You can also put the limits in worksheet cells so they are easy to change. For example, enter a start date in E2 and an end date in F2. If the end date should be included in full, the formula can compare against the day after F2.

With dates in B2:B100, the start date in E2, and the inclusive end date in F2, this counts both boundary days; if E2 is January 5 and F2 is January 15, it counts dates from January 5 through January 15:

=COUNTIFS(B2:B100,">="&E2,B2:B100,"<"&F2+1)

Inclusive and exclusive date boundaries

Decide whether each boundary should count before building the criteria. A condition using “greater than or equal to” includes the start date; “greater than” excludes it. Likewise, “less than or equal to” includes the end date, while “less than” excludes it. For a normal calendar-date column without times, a range with both boundaries included can use greater than or equal to the start and less than or equal to the end.

There is an important reason many Excel users instead use the day after the end with a less-than condition. Excel dates can include times as well as days. A cell that displays January 15 may actually contain January 15 at 2:30 p.m. A condition that is less than or equal to the date value for January 15 includes midnight at the start of that day, but can exclude later times on January 15. Comparing against January 16 exclusively includes every time on January 15.

This approach is also useful for periods that touch without overlapping. For example, January can be counted from January 1 inclusive to February 1 exclusive, and February from February 1 inclusive to March 1 exclusive. A date at midnight on February 1 belongs to February, not both months. This “start inclusive, next period exclusive” pattern is dependable for daily, monthly, and yearly reports.

Counting dates when the cells include times

Excel stores a date and time together as a numeric value: the date represents the day and the fractional part represents the time. Formatting can hide the time, so two cells that look identical may not have the same underlying value. This is why exact matching against a displayed date can miss entries that were recorded at different times during that day.

To count all entries on one particular day, use a lower boundary at the day and an upper boundary at the next day. With date-time values in B2:B100, this counts every entry on January 15, 2025, including those recorded during the day:

=COUNTIFS(B2:B100,">="&DATE(2025,1,15),B2:B100,"<"&DATE(2025,1,16))

If the date-time list contains two entries on January 15—one at midnight and one at 2:30 p.m.—this formula returns 2. A simple equality check for January 15 may count only the midnight value, because the afternoon value includes a time component. The same start-and-next-day method can be applied to each day in a report.

Worked example with an inclusive end date

Imagine B2:B8 contains these seven records: January 1, January 3, January 5, January 10, January 15 at midnight, January 15 at 2:30 p.m., and January 20, 2025. To count records from January 5 through January 15 inclusive, use the following. It returns 4: the January 5, January 10, and both January 15 records.

With those seven date or date-time values in B2:B8, this formula counts January 5–15, 2025, including the whole final day:

=COUNTIFS(B2:B8,">="&DATE(2025,1,5),B2:B8,"<"&DATE(2025,1,16))

Changing the range to begin at January 10 would return 3: January 10 and the two January 15 entries. If you instead wanted dates strictly after January 5, change the first comparison from greater than or equal to to greater than. The key is to express the intended boundary explicitly rather than assuming that Excel will infer whether an endpoint is inclusive.

Make sure Excel has real dates to count

Excel performs date comparisons using stored numeric date values, not just the way a cell looks on screen. A value displayed as “Jan 15” may be a true date, or it may be text that was imported from another system. Formatting text to look like a date does not necessarily convert it into a date value, and text may not behave as expected in date-range criteria.

  • Check a few cells by changing their number format to General. A true date normally appears as a serial number; a text entry remains text.
  • Try sorting the date column from oldest to newest. If some entries stay grouped separately or sort unexpectedly, they may be text or inconsistent values.
  • When importing data, use Excel’s import or conversion tools to make sure the date order matches the source, especially when day and month can be ambiguous.
  • Keep the date cells and the criteria in compatible formats. A display format such as “mmm d” changes appearance, not the stored date.

For a simple, manually entered date, Excel may recognize common date formats according to regional settings. When a workbook is shared across computers with different settings, constructing fixed dates with DATE or entering the bounds in genuine date cells is less ambiguous than typing a date string directly into a formula.

Common mistakes and how to fix them

  • Using COUNTIF for two limits: COUNTIF accepts one condition. Use COUNTIFS when the same date range must meet a start and an end condition.
  • Forgetting the quotation marks or ampersand: A comparison operator must be part of the criteria text, and a cell reference or calculated date must be joined to it. Check that each criteria argument follows the pattern shown in the examples.
  • Including the end date but missing its times: If source values include times, use a less-than comparison against the day after the end date rather than a less-than-or-equal comparison against the end date at midnight.
  • Counting dates stored as text: Convert the source values into real dates before relying on date comparisons. Changing only the number format may not convert text.
  • Using mismatched ranges: In a multi-condition count, make each criteria range the same size and cover the corresponding records. Reusing B2:B100 for both date conditions is appropriate when both limits apply to that one date column.
  • Misreading the month and day: A typed date can be ambiguous across regional settings. Use DATE or validated date cells when the workbook may be opened in different locales.

When to use COUNTIF, COUNTIFS, or a PivotTable

Use COUNTIF when you need one date condition, such as the number of records on or after a cutoff date. Use COUNTIFS when a record must satisfy multiple criteria—for example, a date range and a matching department. The function can apply conditions to different columns as long as the criteria ranges line up row by row.

A PivotTable can be a better choice when you want to summarize a large list by month, year, category, or other fields and interactively explore the results. It requires a little setup and is less direct than a single formula for one fixed count. For a reusable report with chosen start and end dates, a cell-driven COUNTIFS formula is often compact and easy to audit; for a broader summary, a PivotTable may be more convenient.

Frequently Asked Questions

Can COUNTIF count dates between two dates?

Not with two independent conditions in one COUNTIF call. Use COUNTIFS to apply the lower and upper date limits together. The phrase “COUNTIF date range” is common, but the plural function is the appropriate one for a two-sided range.

How do I count dates for one month?

Set the start to the first day of the month and the exclusive end to the first day of the next month. This includes all dates in the requested month and also handles date-time values near the end of the month.

How do I include the end date?

For data that may contain times, use a less-than condition against the day after the requested end date. That includes every time on the end date. For dates with no times, a less-than-or-equal condition against the end date can also work.

Why does my date-range count return zero?

Check whether the source dates are real Excel dates rather than text, whether the start and end values are valid and in the intended order, and whether the criteria ranges cover the data rows. Also confirm that dates were not interpreted with the wrong month/day order during entry or import.

Can the start and end dates come from cells?

Yes. Put the start and end in date cells and refer to them in the criteria. If the end date is inclusive and the source may contain times, compare the date column to a value less than the end-date cell plus one day.

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.