COUNTIFS Not Working With Dates? Fix the Criteria

Yes—COUNTIFS often misses dates because its criteria are written as text, the cells contain text rather than real Excel dates, or the dates include hidden times. With dates in A2:A8, use a start-inclusive and end-exclusive date range; in the sample below, it counts four June dates, including one timestamp: COUNTIFS(A2:A8,">="&DATE(2025,6,1),A2:A8,"<"&DATE(2025,7,1)).

Why COUNTIFS can miss dates

Excel stores a genuine date as a number, with the displayed date supplied by the cell’s number format. A date such as June 15, 2025 is therefore a numeric value, not just the characters shown on screen. COUNTIFS can compare that value with date criteria, but problems arise when the criterion and the cell contents do not represent dates in compatible ways.

Three causes account for many apparent failures. First, a criterion such as a greater-than sign joined to a date must be assembled correctly. Second, imported or manually entered values may look like dates while remaining text. Third, cells may contain times as well as dates, so a criterion for an exact date does not match every entry from that day.

Other issues are less date-specific: the criteria ranges may be different sizes, a referenced cell may be blank, or a formula may point at the wrong column. COUNTIFS returns zero when no row satisfies all the supplied criteria; that result does not by itself tell you which condition failed.

Build date criteria with DATE and comparison operators

For a date interval, join the comparison operator to a date value with an ampersand. DATE(year,month,day) creates a real date value, avoiding ambiguity about whether a typed date is interpreted as month/day/year or day/month/year. The formula in the opening example counts dates on or after June 1, 2025 and before July 1, 2025. In its stated sample, the genuine dates are June 1, June 15, June 30, July 1, and June 15 at 2:30 p.m.; the formula returns 4 because the first three June dates and the timestamp fall in June.

The less-than condition on the first day of the following month is deliberate. It includes every time on June 30, not just entries recorded at midnight. For a month that does not have 31 days, this pattern also avoids having to calculate the final calendar date yourself.

For a selected month, put its first day in a cell and use that cell to construct the boundaries. With the dates in A2:A100 and the month’s first day in D1, use =COUNTIFS(A2:A100,">="&D1,A2:A100,"<"&EDATE(D1,1)). EDATE advances D1 by one month. This version assumes D1 contains a real date, such as June 1, 2025, and returns the number of dates in that month. If you prefer not to use EDATE, supply the following month’s first day as a second cell and refer to it in the upper-bound criterion.

COUNTIFS requires each criteria range to have the same dimensions. In the examples, both date conditions use A2:A100. If you add a status condition, its range must cover the same rows. In Excel for Microsoft 365 on Windows, formulas ordinarily use commas as argument separators; some regional settings display semicolons instead. Excel for Mac and Excel for the web follow the workbook’s regional settings too.

Account for times hidden in date cells

A date-time value includes a fractional part for the time. Formatting a cell to show only the date hides that part but does not remove it. Thus, an entry displayed as June 15 may actually be June 15 at 2:30 p.m. A criterion for equality with June 15 matches the midnight date value, but not necessarily the later timestamp.

To count every entry during one day, set a lower boundary at that day and an upper boundary at the next day. With the sample dates in A2:A8, the two June 15 entries are a date-only value and a 2:30 p.m. timestamp. This formula returns 2: =COUNTIFS(A2:A8,">="&DATE(2025,6,15),A2:A8,"<"&DATE(2025,6,16)).

That same approach works for a month, quarter, or any other period: include the start, exclude the start of the next period. It is generally safer than using a less-than-or-equal-to test against a final date, which can omit times later in that final day. If the intention is to count only entries recorded at exactly midnight, an exact-date criterion may be appropriate; for ordinary daily reporting, the interval is usually the better match.

Check whether the cells contain real dates

Changing a cell’s display format does not convert text into a date. For example, a value imported as the text June 15, 2025 can look convincing after formatting, but Excel may still treat it as text. A date comparison criterion then will not behave as expected. One quick check is to select a suspect cell and temporarily apply a General number format. A genuine date normally appears as a number; text remains text. You can also use Excel’s ISNUMBER function on an individual cell to check whether its underlying value is numeric.

When the data is text, choose a conversion method that fits the source format. If Excel recognizes the text in the computer’s regional settings, you may be able to convert it by using the warning or conversion options Excel offers. For a consistent column from a delimited file, Data > Text to Columns can provide a date-format choice on the final step. Select the source order, such as month-day-year or year-month-day, rather than assuming Excel will infer it correctly. Make a copy of the original column first if the data is important.

DATEVALUE can convert recognizable date text, but its interpretation depends on the date format and locale. An ambiguous entry such as 04/05/2025 could mean April 5 or May 4. Do not convert an ambiguous column until you know the source convention; otherwise, the formula may count valid-looking but incorrect dates. If the source includes timestamps as text, conversion may require handling the date and time together rather than converting only the date portion.

Combine date conditions with other criteria

COUNTIFS applies all its criteria to the same row. That makes it useful for questions such as how many paid orders fell within a particular month. Suppose A2:A8 contains the sample dates and B2:B8 contains statuses: Paid, Paid, Pending, Paid, Paid, Paid, and a blank, in that order. Three paid records fall in June, because the June 30 row is Pending. With that layout, the formula returns 3: =COUNTIFS(A2:A8,">="&DATE(2025,6,1),A2:A8,"<"&DATE(2025,7,1),B2:B8,"Paid").

Check that each range starts and ends on the same rows. A date range of A2:A100 paired with a status range of B2:B99 is not a reliable setup; fix the range references so they cover corresponding records. If the criteria are in cells, check for leading or trailing spaces in text conditions such as Paid, and confirm that the referenced date cell holds a date rather than a text label.

For a rolling period, decide what the period means before writing the criteria. The last 30 calendar days, the current month, and the previous month have different boundaries. Using explicit start and end dates in cells can make a report easier to audit and update than embedding several fixed dates in a long formula.

Common troubleshooting checks

If the result is unexpectedly zero or too small, work through the data and criteria separately rather than changing several parts at once:

Confirm that at least one source cell is a numeric Excel date, not text.
Check whether the source includes times that the displayed format hides.
Use DATE to construct criteria dates instead of relying on ambiguous typed date strings.
Inspect the comparison operators and their quotation marks, and make sure each operator is joined to its date value with an ampersand.
Verify that all criteria ranges have matching dimensions and begin on the intended data row.
Test a small, known interval or a few sample rows, then add other conditions one at a time.
Check for blanks, unexpected spaces in text criteria, and dates outside the intended year.

Be especially careful with year boundaries and month ends. A criterion for dates after December 31 will not include December 31 itself, while a criterion using a less-than boundary for January 1 of the next year will include all times on December 31. Writing down whether each boundary should be included can prevent off-by-one-day errors.

COUNTIFS versus filters and other approaches

COUNTIFS is a good choice when the goal is one count based on several conditions and the criteria ranges are aligned. It recalculates as the source data changes, so it can power a summary cell without manually filtering the table. For a one-time inspection, Excel’s filter controls can be quicker: filter the date column to the desired period and view the visible-row count, while remembering that other active filters also affect what is shown.

If date values are inconsistent, changing to another counting function will not repair the underlying data. Clean or convert the date column first. If you need a visible list of matching records rather than a count, Excel for Microsoft 365 offers FILTER, but COUNTIFS remains simpler when the required answer is just a number. For large reports, a PivotTable can group genuine date values by months or years and summarize records, but it also depends on valid date data and appropriate grouping.

Excel for Mac uses the same COUNTIFS logic and formula structure; ribbon layout and some regional separators can differ. Excel for the web supports COUNTIFS as well. In all versions, the underlying distinction remains the same: a displayed date is not proof that the stored value is a usable date, and a displayed day may conceal a time.

Frequently Asked Questions

Why does COUNTIFS return zero for dates that look correct?

The entries may be text, the criterion may use an ambiguous date string, or the dates may fall outside the selected boundaries. Check the stored values and build criteria with DATE and comparison operators.

How do I count dates in a month with COUNTIFS?

Use a lower bound at the first day of the month and an upper bound at the first day of the next month. Include the first boundary and exclude the second so timestamps on the month’s final day are counted.

Does COUNTIFS ignore times in date cells?

No. A time is part of the stored date-time value, even if the cell format hides it. To include every time during a day, count from that day’s start up to, but not including, the next day’s start.

Can COUNTIFS count dates stored as text?

Do not rely on date comparisons to treat text dates like numeric dates. Convert the source values using a method suited to their known format, then confirm the converted cells contain real dates.

Why does an exact date criterion miss some same-day records?

Some records may have a time later than midnight. An exact match can miss those values; use a start-of-day inclusive boundary and next-day exclusive boundary to count the full day.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.