Fix Excel MAXIFS Errors with DATEVALUE and Named Ranges
Question details
Identify and correct text dates in named ranges used by an Excel MAXIFS formula.

- 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.
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.
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.
Open Formulas > Name Manager, select the date range name, and check its Refers to field. Select the referenced cells on the worksheet.
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.
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.
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.

Check the MAXIFS date criterion and range sizes
Use this method if the dates are numeric but the formula still returns an error or an unexpected maximum.
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. Open the workbook: Open WPS Spreadsheets and choose Open to select your Excel workbook.
- 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. 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. 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.

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.




