Formula Errors

Fix Excel MAXIFS Errors with DATEVALUE and Named Ranges

Algirdas JasaitisAlgirdas Jasaitis Nov 18, 2026 869 views

Question details

Identify and correct text dates in named ranges used by an Excel MAXIFS formula.

Fix Excel MAXIFS Errors with DATEVALUE and Named Ranges
Product
Microsoft Excel
Device & OS
not provided
Scenario
A MAXIFS formula uses named ranges and DATEVALUE to calculate a maximum based on a date criterion.
Observed behavior
The formula produces an error or an unexpected result when referenced values are text instead of valid Excel dates.
Before you start

Confirm that your Excel version supports MAXIFS, then open Formulas > Name Manager to find the ranges used in the formula. Check which range contains the dates and note their text format, such as month/day/year.

Solution 1Recommended

Find and convert text dates in the named range

Use this method when dates in a MAXIFS criteria range look valid but are stored as text.

1
Inspect the named range

Open Formulas > Name Manager, select the date range name, and check its Refers to field. Select the referenced cells on the worksheet.

2
Test a date cell

In an empty cell, enter =ISTEXT(A2), replacing A2 with the first date cell in the named range. TRUE means the value is text. Copy the formula down to check the other dates.

3
Convert the text dates

Select the affected date cells and choose Data > Text to Columns. Click Next twice, select Date, choose the order matching the existing text, such as MDY or DMY, and click Finish.

4
Verify the conversion

In an empty cell, enter =ISNUMBER(A2) for a converted date cell. TRUE indicates a numeric value; check that the displayed date is also correct. Recalculate the MAXIFS formula.

Find and convert text dates in the named range
Check ambiguous dates: Choose the date order that matches the original text. A value such as 03/04/2024 can represent different dates depending on that order.
Work with spreadsheet dates

Check and convert text dates in WPS Spreadsheets

Use WPS Spreadsheets to inspect date values, convert text dates, and recheck a MAXIFS formula in a compatible workbook.

  1. 1. Open the workbook: Open WPS Spreadsheets and choose Open to select your Excel workbook.
  2. 2. Test a date cell: In an empty cell, enter =ISTEXT(A2), replacing A2 with a date cell used by the formula. Copy the test down to find text values.
  3. 3. Convert the dates: Select the text date cells, choose Data > Text to Columns, and select the date order that matches the source values before finishing.
  4. 4. Check the formula: Recalculate the MAXIFS formula and inspect its result. Confirm that your WPS Spreadsheets version supports MAXIFS and that the referenced ranges have matching dimensions.
Identify text dates with ISTEXTConvert date text with Text to ColumnsOpen and save Microsoft Excel XLSX filesUse a familiar spreadsheet interface
QA img-9

Frequently Asked Questions

How can I tell if an Excel date is stored as text?

Enter =ISTEXT(A2) in an empty cell, replacing A2 with the date cell. TRUE means the value is stored as text.

Why does DATEVALUE return an error for a date?

DATEVALUE may not recognize the text's date format. Test the text in an empty cell with =DATEVALUE("1/1/2024"), or use =DATE(2024,1,1) to specify the year, month, and day numerically.

Why does MAXIFS return zero instead of the expected maximum?

MAXIFS returns 0 when no cells meet the criteria. Check whether the date range contains text dates and whether the criterion uses the intended date and comparison operator.

Do MAXIFS ranges need to be the same size?

Yes. The maximum range and each criteria range must have the same dimensions. Check their references under Formulas > Name Manager.