Fixing Excel SUMIFS That Returns Zero When Date Criteria Use Text-Formatted Cells

You've written a SUMIFS formula that sums sales by date range, and it returns zero. You double-check the criteria, confirm the dates are in the right cells, and still get nothing. The problem almost certainly isn't your formula logic — it's that some of your date cells are stored as text, not as real Excel dates, and SUMIFS can't compare them.

This is one of the most common silent failure modes in Excel. The cells look like dates, they might even display with a date format applied, but internally Excel treats them as strings. A text string like "2024-03-15" and the date serial number 45366 are not equal to Excel, so every comparison fails and your sum comes back zero.

What You'll Learn

• How Excel stores dates internally and why text dates break comparisons
• Three reliable ways to identify text-formatted date cells
• Four concrete fixes you can apply right now, from quick inline fixes to permanent column conversions
• The double-unary trick that handles ambiguous criteria without restructuring your sheet
• Pitfalls to avoid so the problem doesn't silently return

Why SUMIFS Returns Zero With Date Criteria

SUMIFS compares each cell in the criteria range against the criterion you specify. When your criterion is a real date — say, >=DATE(2024,1,1) — Excel compares it against the serial number stored in each criteria-range cell. If those cells contain the text string "01/01/2024" instead of the serial number 45292, the comparison fails every single time. No matches, sum of zero.

This happens most often when dates come in from an external source: a CSV export, a database query, a copied web table, or a colleague's file saved on a system with different locale settings. Excel doesn't always auto-convert those values on import. Even pasting from another application can land dates as text.

There's also a subtler variant: your criteria cell contains a text date rather than your source column. If cell E1 holds the string "2024-03-01" and your formula reads SUMIFS(C:C, B:B, ">="&E1), the concatenated criterion becomes the string >=2024-03-01, which Excel cannot evaluate as a date comparison.

How Excel Stores Dates Internally

Every real date in Excel is a positive integer called a serial number. January 1, 1900 is serial 1; January 1, 2024 is serial 45292. When you format a cell as a date, Excel just changes how that number displays — the underlying value stays numeric.

Text strings that look like dates have no serial number. They are sequences of characters. Excel can display them with a date format applied on top, which makes them visually indistinguishable from real dates — until a formula tries to do arithmetic or comparison on them.

This distinction is what breaks SUMIFS. The function needs two comparable numeric values (or two comparable text strings). A numeric serial on one side and a text string on the other produces no match.

Spotting Text-Formatted Date Cells

Before picking a fix, confirm you actually have a text-date problem. Three quick checks:

Check 1: Left-alignment in the cell

Real dates, like all numbers, default to right-alignment. Text defaults to left-alignment. If your date column is left-aligned and you haven't manually changed the alignment, that's a strong signal the values are text.

Check 2: The ISNUMBER test

In a blank cell next to one of your dates, type:

=ISNUMBER(B2)

If the result is FALSE, the cell holds a text value, not a real date. Run this on a few cells across your date column to see how widespread the problem is.

Check 3: The green triangle indicator

Excel sometimes flags numbers (and dates) stored as text with a small green triangle in the top-left corner of the cell. Click one of those cells and you'll see a warning icon offering to convert it. This indicator doesn't always appear — Excel only shows it when it's confident the text looks like a number — but when it does, it's definitive.

If COUNTIFS with date criteria is also misbehaving for you, the same text-date problem is likely the root cause — the diagnostic steps in fixing COUNTIFS that returns wrong counts with date ranges walk through identical checks.

Fix 1: Use DATEVALUE to Convert Criteria Inline

If your criteria cells (not the source column) are text dates, the fastest fix is to wrap your criterion reference in DATEVALUE() directly inside the formula. This converts the text criterion to a serial number at evaluation time without touching any data.

=SUMIFS(C:C, B:B, ">="&DATEVALUE(E1), B:B, "<="&DATEVALUE(F1))

DATEVALUE(E1) takes the text string in E1 and returns its date serial number. Concatenating that with >= gives SUMIFS a valid numeric comparison string. This works as long as the text in your criteria cells is in a recognizable date format (ISO, US, or matching your locale).

If your source column (column B here) is the text-date column, this fix alone won't be enough — read on.

Fix 2: Convert the Source Column to Real Dates

The cleanest long-term solution is converting your text-date column to real dates in place. This is the right move when you own the data and don't need to preserve the original text values.

Option A: Text to Columns

1. Select the date column (e.g., column B).
2. Go to Data → Text to Columns.
3. Choose Delimited, click Next, uncheck all delimiters, click Next again.
4. Under Column data format, select Date and choose the format that matches your text (MDY, DMY, or YMD).
5. Click Finish.

Excel will re-parse each text string as a real date serial number. Your column formatting might reset, so reapply a date format if needed. This is the most reliable bulk conversion for consistent text-date formats.

Option B: Paste Special — Multiply by 1

This works only when the text dates happen to be in a format Excel's default locale recognizes directly:

1. Type 1 in an empty cell and copy it.
2. Select your text-date column.
3. Right-click → Paste Special → Multiply → OK.

Multiplying by 1 forces Excel to re-evaluate each cell as a number. If the text is parseable, it becomes a serial. If not, you'll get #VALUE! errors, which at least makes the problem visible.

Fix 3: Use a Helper Column for Converted Dates

When you can't modify the source column (shared file, protected sheet, live data feed), add a helper column that converts the text dates to serials, then point your SUMIFS at that column instead.

=DATEVALUE(B2)

Fill that formula down alongside your data. Format the helper column as a date so it's readable. Then update your SUMIFS to reference the helper column:

=SUMIFS(C:C, D:D, ">="&DATE(2024,1,1), D:D, "<="&DATE(2024,3,31))

This approach is non-destructive and easy to audit. The original data stays untouched, and you have a clearly labeled column of real dates to work from. If the source data refreshes automatically, the helper column recalculates too.

Similar helper-column patterns appear when SUMIFS misbehaves due to mixed data types — isolating the problematic column in a parallel calculated column is a broadly useful pattern.

Fix 4: Wrap Criteria in VALUE or -- (Double Unary)

The double-unary operator (--) coerces a value to a number. Applied to a text date, it produces the serial number. This is particularly handy inside array-style formulas or when your criteria reference is already a cell you don't want to modify.

=SUMIFS(C:C, B:B, ">="&--E1, B:B, "<="&--F1)

The --E1 piece works exactly like DATEVALUE(E1) for text dates that match Excel's locale format. It's slightly more fragile — if the locale doesn't recognize the text format, -- returns a #VALUE! error — but it's concise and widely used.

You can also use VALUE() as a named equivalent:

=SUMIFS(C:C, B:B, ">="&VALUE(E1))

Both -- and VALUE() apply only to the criteria, not the source column. If the source column holds text dates, you still need Fix 2 or Fix 3 alongside this one.

If you're running into related formula failures where criteria logic gets complicated, it's worth reviewing how COUNTIFS handles OR logic in criteria — the same coercion patterns apply.

Common Pitfalls When Fixing Date Criteria

Applying a date format doesn't fix the underlying data

Formatting a text cell as a date changes how it looks, not what it is. A text string formatted as a date still returns FALSE from ISNUMBER(). Don't confuse visual formatting with data type conversion.

DATEVALUE is locale-sensitive

DATEVALUE("03/04/2024") returns March 4 on a US system and April 3 on a UK system. If your file travels between locales or your data comes from a different locale, always prefer the unambiguous ISO format ("2024-03-04") or build dates with DATE(year, month, day) to avoid misinterpretation.

Mixing real dates and text dates in the same column

A partially imported column might have some real dates and some text dates side by side. SUMIFS will match only the real ones. Run ISNUMBER() checks across the full column — not just the first few rows — before deciding on a fix. This partial-mismatch scenario is also discussed in the context of SUMIF returning wrong totals due to range inconsistencies.

Hard-coding date strings in criteria

Avoid writing criteria like SUMIFS(C:C, B:B, ">=01/01/2024"). The concatenated string is locale-dependent and often doesn't parse as a date comparison. Always use DATE(), a cell reference coerced with DATEVALUE(), or a numeric serial:

=SUMIFS(C:C, B:B, ">="&DATE(2024,1,1))

Time components breaking date comparisons

If your source dates include a time component (e.g., 2024-03-15 14:30:00), the serial number includes a decimal fraction. Comparing against a whole-number date like DATE(2024,3,15) with >= still works, but =DATE(2024,3,15) will match nothing because the exact serials differ. Use a range (>= the start date and < the next day) when your data has timestamps.

Wrapping Up

Text-formatted dates are a persistent source of silent failures in Excel formulas. Once you know what to look for — left-aligned cells, ISNUMBER() returning false, missing green triangles — the diagnosis takes under a minute. The fix you choose depends on whether you can modify the source data:

• Criteria cells are text: Wrap them in DATEVALUE() or use the -- double unary inside your formula.
• Source column is text, you can edit it: Use Text to Columns (Data tab) to bulk-convert in place.
• Source column is text, you can't edit it: Add a helper column with =DATEVALUE(B2) and point SUMIFS there.
• Mixed real and text dates in the same column: Run ISNUMBER() across the full range to map the damage, then use Text to Columns or a helper column to normalize everything.
• Dates with time components: Switch your criteria to a range using >= start and < start+1 to avoid serial-number mismatches.

Once you've converted or coerced your dates properly, SUMIFS will match exactly what you expect, and your zero-result headaches disappear for good.