Excel COUNTIFS Not Working With Dates? Fix the Criteria

Excel COUNTIFS usually fails with dates because the cells contain text instead of real date values, the criteria are joined incorrectly, or the data includes times that the criteria do not account for. Check that the dates are numeric, then build criteria by joining an operator such as “>=” to a date cell; for a day that may contain times, use a start boundary and an exclusive next-day boundary.

How COUNTIFS evaluates dates

Excel stores genuine dates as serial numbers and displays them using a date format. The displayed value, such as 3/15/2026, is therefore only a presentation of an underlying number. COUNTIFS compares that number with the criteria you give it. A date that looks correct on screen can still be text, and changing its number format does not convert text into a real date.

COUNTIFS applies multiple criteria to corresponding positions in its ranges. It counts a row only when every condition is true. For example, you can count records whose date falls in a period and whose status is “Paid.” The criteria range for the date and the criteria range for the status must cover matching rows and have the same dimensions.

A criterion can be a date value, a cell reference, or a text expression containing a comparison operator and a value. When the operator is fixed but the date is in a cell, concatenate them. If the start date is in E2 and the end date is in F2, and dates are in A2:A100, use this formula for a period that includes the start date but stops before the end date:

With dates in A2:A100, the start date in E2, and the end boundary in F2, this counts dates on or after E2 and before F2: =COUNTIFS($A$2:$A$100,">="&E2,$A$2:$A$100,"<"&F2)

For example, if E2 is March 1 and F2 is April 1, the count includes dates in March. The exclusive upper boundary is useful even when the date column includes times, as explained below.

Use the right date criteria syntax

A common mistake is putting a comparison operator and a cell reference together as if they were one literal string. Excel needs the operator text joined to the referenced date value. The ampersand performs that join; the date cell itself should contain a genuine date.

With dates in A2:A100 and the cutoff date in E2, this counts dates on or after the cutoff:

=COUNTIFS($A$2:$A$100,">="&E2)

With dates in A2:A100 and a single date in E2, this counts exact matches to that date:

=COUNTIFS($A$2:$A$100,E2)

Exact matching is appropriate when every entry is a date without a time component. For a date column that may include times, an entry such as March 15 at 2:30 p.m. is not equal to the serial value for March 15 at midnight. Use a range covering the whole day instead.

With date-and-time values in A2:A100 and the date to count in E2, this counts every value on that calendar day:

=COUNTIFS($A$2:$A$100,">="&E2,$A$2:$A$100,"<"&E2+1)

The first condition includes midnight at the start of E2. The second excludes midnight at the start of the following day, while including every time earlier that day. This is more dependable than trying to guess the last possible time value.

Check whether the cells contain real dates

Start with a suspicious date cell and test whether Excel recognizes its contents as a number. With a suspected date in A2, this returns TRUE for a genuine Excel date value and FALSE for text:

=ISNUMBER(A2)

Cell formatting is not a reliable test: text can be formatted to look like a date without becoming numeric. You can also change a suspected cell temporarily to General format. A real date usually appears as a serial number; text remains text. Restore the desired date format afterward.

Text dates often come from imported CSV files, copied reports, or systems that use a different date order. For instance, 04/05/2026 can mean April 5 or May 4 depending on the source’s convention. Do not convert ambiguous text until you know which order the source intended.

For a recognized text date in A2, DATEVALUE can convert it to an Excel date serial. With the text date in A2, use: =DATEVALUE(A2). Check that the result is interpreted as the intended day, month, and year.
For a whole imported column, select the cells and use Data > Text to Columns. Follow the wizard, choose the source date order where prompted, and finish the conversion. Make a copy first if the original text must be preserved.
If conversion depends on the computer’s regional settings, verify several known dates before relying on the count. Reformatting alone does not repair text values.

After conversion, apply a date format and check that the resulting values are plausible. If only some entries are text, COUNTIFS may count the valid dates and silently ignore the text entries, making a partial conversion especially easy to miss.

Account for times hidden in date cells

Excel date-and-time values combine a whole-number date with a fractional part representing the time. Formatting a cell to show only a date hides that fraction; it does not remove it. This explains why a formula that appears to search for an exact displayed date can return zero even though the row looks like a match.

For one day, use a lower boundary at the beginning of the day and an upper boundary at the beginning of the next day. For a month, use the first day of the month and the first day of the next month. The same approach works for weeks or custom reporting periods: choose a clearly defined start and end, and make the end exclusive.

With date-and-time values in A2:A100, this formula counts March 2026, including every time on March 31:

=COUNTIFS($A$2:$A$100,">="&DATE(2026,3,1),$A$2:$A$100,"<"&DATE(2026,4,1))

The result is the number of entries from March 1 at 00:00 through March 31 at 23:59:59… at the precision represented in the cells. Excel dates have finite numeric precision, but the practical rule is simple: exclude the next period’s starting boundary rather than relying on a final time of day.

Build a COUNTIFS formula for a real report

Suppose column A contains transaction dates, column B contains payment status, and E2 and F2 hold the beginning and ending boundaries of a reporting period. The report should count paid transactions from the start date through the day before the end date. If the date column may include times, use this formula:

With dates or date-times in A2:A100, statuses in B2:B100, the start boundary in E2, and the exclusive end boundary in F2, this counts paid rows in the period:

=COUNTIFS($A$2:$A$100,">="&E2,$A$2:$A$100,"<"&F2,$B$2:$B$100,"Paid")

Both date conditions refer to the same date range, while the status condition checks the corresponding row in B. If E2 is March 1 and F2 is April 1, the formula counts paid transactions in March. If the count is unexpectedly low, verify the status spelling and spaces as well as the dates; a date formula can be correct while another condition excludes the rows.

Keep criteria ranges aligned. For example, using A2:A100 for dates and B3:B101 for statuses compares different rows and can produce misleading results. In general, use ranges with the same starting row, ending row, and shape.

Common reasons the count is zero or too small

If the formula returns zero, inspect the data and criteria in a deliberate order rather than rewriting the entire formula. A date criterion can fail for several independent reasons:

Dates are stored as text. Test representative cells with ISNUMBER and convert the source values if needed.
The operator is not concatenated with the date. For a variable boundary, the criterion needs a form such as ">="&E2, not a quoted phrase that literally contains the cell reference.
The date cell is not a date. A blank, text value, or misinterpreted regional date in the criteria cell can make the comparison fail or use the wrong boundary.
Times are present. Exact equality to a date at midnight does not match values later that day. Use the two-boundary method.
The ranges differ in size or alignment. Ensure every criteria range covers the same records in the same order.
Another criterion filters out the row. Check status, category, spelling, and extra spaces independently. COUNTIFS requires all conditions to match.
The period boundaries are reversed or unintended. Confirm the start date is earlier than the exclusive end date and that both cells contain the expected year.

To isolate a problem, temporarily test the date condition by itself, then add the other criteria one at a time. If the date-only count is correct but the combined count is not, investigate the added conditions. If the date-only count is wrong, focus on the date values, boundaries, and time portions.

Dates, text criteria, and regional settings

Typing a date directly into a formula can be less clear than referring to a date cell. Date text written in a formula may be interpreted according to regional conventions, and a string such as 3/4/2026 is ambiguous across date orders. A cell containing a verified date makes the intended boundary visible and easier to change.

DATE(year,month,day) is another clear way to create a date boundary inside a formula. It avoids relying on a typed date string, but the arguments still need to reflect the intended year, month, and day. In DATE, the month is the second argument and the day is the third; reversing them changes the result.

Excel’s display settings can also differ from the source system. A date may appear as 5/4/2026 in one locale and 04/05/2026 in another, while the underlying serial value is the same once correctly interpreted. When exchanging workbooks, verify the actual value and intended date rather than assuming the visible order has the same meaning everywhere.

When to use a different formula or tool

COUNTIFS is a good choice when you need to count rows that meet several conditions, such as a date period plus a department or status. It is available in current desktop versions of Excel, including Microsoft 365 for Windows, and is also available in Excel for Mac and Excel for the web. For a simple one-condition count, COUNTIF can be shorter, but it does not support multiple criteria ranges.

If dates are mixed with explanatory text or inconsistent formats, clean the source column before building increasingly complicated criteria. A helper column can make the data issue visible and keep the final count understandable. For a recurring report, converting imported dates once and using cell-based boundaries is generally easier to maintain than embedding date strings in each formula.

Filtering the table by date is a useful visual check, but it is not a substitute for correcting text dates or aligning formula ranges. Likewise, formatting a cell as Date changes how a numeric value is displayed; it does not make arbitrary text eligible for numeric date comparisons.

Frequently Asked Questions

Why does COUNTIFS return zero when the dates look correct?
They may be text, the criteria may be joined incorrectly, or the date cells may include times. Test a sample with ISNUMBER, check the criteria cell, and use a start-and-next-day boundary if the records contain times.

How do I count dates between two dates in Excel?
Use one condition for dates greater than or equal to the start and another for dates less than the end boundary. Make the end boundary the day after the final included date if you want to include that entire final day.

Can COUNTIFS count dates that also contain times?
Yes. Use a lower boundary at the beginning of the target period and an exclusive upper boundary at the beginning of the next period. Exact equality to a date generally misses times later in that day.

Does changing a cell to Date format convert text into a date?
No. Number formatting changes the display of values Excel already recognizes as numbers. Convert text dates using a suitable method, then verify that Excel interpreted their day, month, and year correctly.

Why does my monthly COUNTIFS miss the last day of the month?
The last day may contain times that are later than midnight, so a condition that ends at the last date alone can omit them. Use the first day of the following month as an exclusive upper boundary.

Do all COUNTIFS criteria ranges need to be the same size?
Yes. Use ranges with matching dimensions and corresponding rows. A mismatch can cause an error or, more importantly, make the formula compare criteria from different records.

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.