To count dates between two boundaries in Excel, use COUNTIFS with a lower-bound and an upper-bound condition. With dates in A2:A9 and the start and end dates in D2 and E2, enter =COUNTIFS(A2:A9,">="&D2,A2:A9,"<="&E2) to count dates on or after D2 and on or before E2.
Why COUNTIFS Is the Right Function
Although people often search for COUNTIF between dates, the standard way to apply two date conditions to the same range is Excel’s COUNTIFS function. COUNTIF accepts one condition; COUNTIFS can apply multiple conditions, including a start date and an end date. Both functions are available in Microsoft 365 for Windows.
The distinction matters because a date range normally has two limits. One condition says a value must be greater than or equal to the beginning; another says it must be less than or equal to the end. COUNTIFS counts a row only when both conditions are true. The formula returns a number, not a filtered list of the matching dates.
In the opening formula, the ampersands join each comparison operator to the date stored in a cell. Excel needs the operator and the date together as a criterion, such as greater than or equal to the value in D2. The quotation marks make the operator a text string; the cell reference supplies the date value.
Set Up the Dates and Boundaries
For a straightforward example, suppose A2:A9 contains these entries: January 3, January 10, January 15, January 15, January 20, January 31, February 1, and a blank cell. Enter January 10, 2025, in D2 and January 20, 2025, in E2. The formula below returns 4: January 10, both January 15 entries, and January 20. The endpoints are included because the conditions use >= and <=.
For this example, with the dates in A2:A9 and the start and end dates in D2 and E2, use =COUNTIFS(A2:A9,">="&D2,A2:A9,"<="&E2). It counts four matching date cells. If your data has more rows, adjust both occurrences of A2:A9 to cover the full date column range; the two criteria ranges in COUNTIFS must be the same size and shape.
Enter real Excel dates in the cells, rather than text that only looks like a date. You can type a date in a format Excel recognizes for your regional settings, or construct one with the DATE function. To avoid ambiguity in a formula, DATE(year,month,day) is a dependable way to specify a date. For instance, DATE(2025,1,10) represents January 10, 2025.
Choose Inclusive or Exclusive Boundaries
The comparison operators determine whether the boundary dates count. Use >= for an inclusive start and <= for an inclusive end. If the rule excludes both boundary dates, use > and <. If it includes the start but excludes the end, use >= and <. Match the formula to the question being answered, such as counting activity during a period that begins on one date but ends at the start of another.
With dates in A2:A100 and the start date in D2 and end date in E2, this version counts values strictly after D2 and strictly before E2: =COUNTIFS(A2:A100,">"&D2,A2:A100,"<"&E2). The result depends on the dates in the range; neither boundary itself qualifies.
It is also possible to use fixed dates directly in the formula. For example, with dates in A2:A100, this counts dates from January 10 through January 20, 2025, inclusive: =COUNTIFS(A2:A100,">="&DATE(2025,1,10),A2:A100,"<="&DATE(2025,1,20)). Using DATE avoids relying on how Excel interprets a typed date such as 1/10/2025, which may mean different things in different regional settings. Cell references are usually easier to maintain when users need to change the period.
Handle Dates That Include Times
A date and time in Excel is a number with a fractional part: the date represents the day, and the fraction represents the time of day. This creates a common boundary problem. If E2 contains January 20, 2025, with no time, then the condition less than or equal to E2 includes midnight at the start of January 20, but not later times that same day.
To count every timestamp from the start date through the entire end date, make the upper limit exclusive and set it to the day after E2. With timestamps in A2:A100 and date-only boundaries in D2 and E2, use =COUNTIFS(A2:A100,">="&D2,A2:A100,"<"&E2+1). The condition includes any time on the end date, while excluding midnight on the following day. For example, if the range contains January 20 at 5:00 p.m., this approach includes it.
This technique is useful for logs, sales records, and other data with time-of-day values. It also avoids having to guess the last possible time of a day, such as 11:59:59 p.m. If your source values are date-only, the inclusive formula is simpler and works as expected.
Count Dates in a Month or Other Period
For a monthly total, build the period boundaries from a date in the target month. The first condition is on or after the first day of that month; the second is before the first day of the next month. Using an exclusive next-month boundary works whether the source data contains date-only values or timestamps.
With dates or timestamps in A2:A100 and any date in the month to count in D2, enter =COUNTIFS(A2:A100,">="&DATE(YEAR(D2),MONTH(D2),1),A2:A100,"<"&DATE(YEAR(D2),MONTH(D2)+1,1)). If the example dates listed earlier are in A2:A9 and D2 is any date in January 2025, the result is 6: the six January entries, excluding the blank and February 1. DATE automatically rolls a month value of 13 into January of the following year.
The same pattern can define other reporting periods. Replace the calculated start and end boundaries with cells or expressions for the period you need, and keep the end condition exclusive when timestamps may occur on the final day. For a quarter or a custom reporting window, make sure the calculated boundaries truly match the organization’s definition of that period.
Check the Data If the Count Looks Wrong
Most unexpected results come from the contents of the date cells or from the chosen boundaries, not from the basic COUNTIFS pattern. Work through these checks:
- Confirm the values are dates. Changing a cell’s number format does not turn text into a date. A value stored as text may not compare as expected. If necessary, convert or re-enter the values as real dates, then test a few cells by changing their number format to General and checking whether they display as numbers.
- Inspect hidden times. A cell can display only a date while retaining a time value. The exclusive next-day condition handles this for a complete end date.
- Check the boundaries. Confirm that the start is not later than the end, and that the operators reflect whether endpoints should be included.
- Make the ranges match. In COUNTIFS, every criteria range should have the same dimensions. Use the same date range for both conditions when you are testing one column against two limits.
- Look for inconsistent entries. Blanks are not counted as qualifying dates by these greater-than-or-equal and less-than comparisons, but text, errors, or malformed imported values can make a dataset behave differently from what its display suggests.
Formatting can make valid dates easier to read, but formatting alone does not repair text values. If data was imported from a website or another system, inspect representative cells and verify that Excel recognizes them as dates before relying on a date-range count.
COUNTIF, COUNTIFS, and Alternative Approaches
Use COUNTIF when a single condition is enough, such as counting dates on or after one start date. Use COUNTIFS when you need both ends of a range, or when the count also depends on another field. For example, you could count records for one department whose dates fall within a reporting period by adding a department range and matching department criterion to the date conditions.
For a two-bound date count, COUNTIFS is usually clearer than subtracting one COUNTIF result from another. It states both limits together and is easier to check when someone reviews the worksheet. If the goal is to display the matching records rather than just return a count, a filtering approach may be more suitable; counting and listing are different tasks. In Microsoft 365, FILTER can return records that meet conditions, but it is not a replacement when you only need the number.
Excel uses dates as serial numbers, so comparisons are numeric comparisons under the surface. This is why a date criterion can be combined with operators such as >= and <. You generally do not need to convert dates to text or use a special date-counting function. Keeping the source dates as genuine date values preserves sorting, calculations, and other date-based analysis.
Frequently Asked Questions
Can COUNTIF count between two dates?
Not with two criteria in a single COUNTIF function. Use COUNTIFS to apply both the beginning and ending date conditions to the same range.
Does COUNTIFS include the start and end dates?
It does when the formula uses >= for the start and <= for the end. Replace those operators with > or < if the corresponding boundary should be excluded.
Why does my formula miss entries on the end date?
If the cells contain times, a date-only end boundary 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 end date.
Can I use dates typed directly in the formula?
Yes. Use DATE(year,month,day) to create clear date boundaries, or reference cells containing valid Excel dates. DATE is especially useful when a formula needs to work consistently across regional date formats.
Why does the formula return zero when dates are visible?
The entries may be stored as text, the date range may not include the relevant cells, or the start and end values may be reversed. Check that Excel recognizes the entries as dates and verify the criteria operators and cell references.
Build a Reliable Date Count
For most date-range questions, the dependable pattern is a pair of COUNTIFS criteria: one sets the lower boundary and the other sets the upper boundary. Decide whether each endpoint is included, account for times when they are present, and ensure the cells contain real dates. Once those details are right, the same formula structure can support daily checks, monthly reports, and other date-based summaries without changing the underlying data.
Related
- Change Block Capitals to Lowercase in Excel
- Center Across Selection in Excel: Add It to Quick Access
- Cell in Microsoft Excel: Meaning, Types, and How to Use It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- COUNTIF Function to Find Duplicates in Excel: A GuideYes. In Excel, COUNTIF can find duplicates by counting how often each value appears: with values in A2:A7,…
- Excel CONCATENATE: Does It Keep Text Formatting?No. Excel’s CONCATENATE, CONCAT, and TEXTJOIN functions combine values into text, but they do not preserve rich-text styling…
- How to Show Formulas in Excel for MacTo show formulas in Excel for Mac, open the worksheet and choose Formulas > Show Formulas. You can…
- Number Sequence in Excel: How to Continue a SeriesTo continue a number sequence in Excel, enter the first two values, select both cells, then drag the…
- Excel INDIRECT: Link to Another Tab with a FormulaTo refer to a cell on another worksheet using a sheet name stored in A1, enter =INDIRECT("'"&A1&"'!B2"). If…
- Excel Formula for Workdays Between Dates: NETWORKDAYSUse Excel’s NETWORKDAYS function to count Monday-to-Friday workdays between two dates, optionally excluding holidays: =NETWORKDAYS(start_date,end_date,[holidays]). It counts both…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.