Excel MAX Date Not Working? Fix Text Dates and Errors

If the cells contain real Excel dates, MAX returns the latest one: with dates in B2:B100, enter =MAX(B2:B100). For example, if the latest value in that range is December 31, 2025, the result is that date; if the result is wrong or appears as zero, check whether the dates are stored as text, whether the range contains errors, and how the result cell is formatted.

How MAX finds the latest date

Excel stores a date as a serial number, not as a special text label. In the standard 1900 date system, later calendar dates have larger serial numbers, so the largest number in a range of dates is the latest date. A cell may display “Dec 31, 2025,” while its underlying value is a number that Excel can compare.

The MAX function returns the largest numeric value in its arguments. When the argument is a cell reference or range, MAX ignores blank cells and text, including date-looking text. That distinction explains many cases where dates look correct on screen but MAX does not include them. Formatting a cell to look like a date changes its appearance; it does not turn text into a date value.

MAX also does not know which dates are relevant to your question. It simply finds the greatest number in the range. If you need the latest date for one customer, category, or status, use a criteria-based calculation rather than expecting MAX to infer the condition.

First checks when the answer is wrong

Start by checking the exact range and the cells that appear to contain the latest dates. Then look for these common causes:

Text dates: A value such as “12/31/2025” may be text rather than an Excel date, particularly after importing a CSV or copying data from a website.
Errors in the range: Values such as #VALUE! or #N/A can cause MAX to return an error instead of a date.
A display format that hides the result: MAX may have returned a valid serial number, but the result cell is formatted as General or Number, or its date format omits the time.
An incomplete range: The formula may stop above the newest row, or include the wrong column.
Filters or hidden rows: MAX includes hidden and filtered-out rows. It does not automatically limit its result to visible records.
Dates mixed with times: A later time on the same day is a larger value, even if the cell display shows only the date.

To check a suspicious cell, with a date or date-looking value in A2, select the cell and look at the formula bar, or temporarily change its number format to General. A genuine date displays as a number; a text date remains text. You can also check the value type: with the value in A2, =ISNUMBER(A2) returns TRUE for a numeric date and FALSE for text. For a date range, test a few cells that were imported or manually entered rather than assuming the whole column has one consistent type.

Convert dates stored as text

For a column of consistently formatted text dates, a quick conversion is often available from the ribbon. Select the cells, choose Data > Text to Columns, select Delimited, click Next twice, choose Date and the matching order (such as MDY or DMY), then click Finish. Check a few converted cells afterward; choosing the wrong order can swap the month and day or create incorrect dates.

For a smaller number of values, with a text date in A2 that Excel can interpret under your regional date settings, =DATEVALUE(A2) converts it to a date serial. For example, if A2 contains the text “3/14/2025” and the locale recognizes month/day/year, the result represents March 14, 2025. Copy the converted results and use Paste Special > Values if you want to replace the original text. DATEVALUE depends on how the text is written and on regional settings, so it may fail or interpret ambiguous values differently on another computer.

Another option is to re-import the source file and explicitly choose the date format in the import process. This is useful when a recurring CSV import keeps producing text dates. Avoid simply applying a date number format to text: the display may not change, because the underlying value is still text. Once converted, format the cells using Home > Number Format and choose a date format that makes the values easy to verify.

Check formatting, times, and date systems

If MAX appears to return a number such as 46022, it may already be correct: that is a date serial, not a failed calculation. Select the result cell and choose Home > Number Format > Short Date, or use Ctrl+1 on Windows to open Format Cells and choose a date format. On Mac, use Command+1 to open Format Cells. If you see #######, widen the column first; Excel often uses that display when a date or number does not fit in the available width.

Excel dates can include a time fraction. For instance, a value representing 4:00 p.m. is later than a value for 9:00 a.m. on the same date. MAX therefore returns the later timestamp, though a format showing only the day makes both values look identical. Apply a date-and-time format if the time matters. Conversely, if you want the latest calendar day regardless of time, the result is usually still on that day, but hidden timestamps can affect comparisons with other criteria or records.

Excel workbooks can use the 1900 or 1904 date system. The same calendar date has serial values that differ by 1,462 days between these systems. This can matter when moving numeric date values between older workbooks or systems; it is not a reason to add or subtract days blindly. Check the workbook’s date-system setting under File > Options > Advanced, in the When calculating this workbook section on Windows. Mac’s setting is under Excel > Settings (or Preferences) > Calculation. If dates shift after a transfer, verify that they were transferred as dates and review both workbooks’ date-system settings.

Handle errors, blanks, and incomplete ranges

MAX ignores empty cells and text in a referenced range, but an error value in the range can make the result an error. Find the error cells and correct the underlying formula or source data where possible. Do not hide every problem with a broad error-handling formula until you understand why it occurred; doing so can make a bad date look like a valid result.

If the result is zero, the range may contain no numeric dates—for example, because all visible entries are text—or the range may truly be empty. A zero formatted as a date can display oddly, such as a day-zero-looking date, depending on the format and date system. Confirm that at least one cell is a numeric date before treating zero as a meaningful latest date.

Also inspect the formula’s range boundaries. If new records are added below row 100 but the formula references B2:B100, those records cannot affect the answer. Excel Tables can help keep ranges aligned as records are added: convert the data to a table with Ctrl+T on Windows (Command+T on Mac in supported contexts), and use the table’s date column in your calculation. If the data contains headers, make sure the calculation references the date column rather than an unrelated field.

Find the latest date that meets a condition

For a latest date tied to a category, use MAXIFS in Microsoft 365 or Excel 2019 and later. With categories in A2:A100, dates in B2:B100, and the criterion “East,” enter =MAXIFS(B2:B100,A2:A100,"East"). If the matching East rows contain May 3, May 9, and May 7, 2025, the result is May 9, 2025. The date range and each criteria range must have matching dimensions.

MAXIFS only considers numeric dates that meet the condition, so text dates remain a problem even when the formula is otherwise correct. If there are no matching rows, it returns 0; format and inspect the result, and verify that the criterion matches the source data exactly. For older Excel versions without MAXIFS, use a compatible approach such as filtering the data and finding the maximum of the resulting date values, or update Excel if your organization permits it.

For a status such as “Closed,” the same pattern works with the status column as the criteria range and “Closed” as the criterion. If a criterion is stored in a cell, refer to that cell instead of typing the text into the formula. Check for extra spaces or inconsistent labels when a formula unexpectedly finds no matches.

Visible rows and alternative approaches

Filtering a list does not make MAX ignore the rows hidden by the filter. If the question is specifically “What is the latest date among the visible rows?”, do not assume that a normal MAX formula answers it. SUBTOTAL can calculate a maximum while ignoring rows excluded by a filter, but it does not provide the same direct, general-purpose criteria handling as MAXIFS. For a result that must respond to several conditions and visibility rules, a PivotTable, a carefully designed helper column, or a modern dynamic-array formula may be more appropriate.

A PivotTable is useful when you want to compare latest dates across many categories rather than show just one answer. Put the category in Rows and the date field in Values, then change the value calculation to Max if Excel offers that summary for the field. If the date field is being treated as text, the PivotTable may not summarize it as a date; correct the source data first, then refresh the PivotTable. This can also reveal which categories have no valid date values.

Frequently Asked Questions

Why does MAX return 0 for my dates?
If the referenced cells contain only blanks or text dates, MAX has no numeric date to return and can produce 0. Convert the text to real dates, check that the formula covers the correct cells, and confirm that the range includes at least one valid date.

Does changing the cell format convert text to a date?
No. Formatting changes how a value is displayed, not what it is. Convert text using Text to Columns, DATEVALUE when the text is interpretable, or a correctly configured import; then apply a date format.

Why does MAX show a number instead of a date?
Excel dates are serial numbers. Format the result cell as a date using Home > Number Format, or open Format Cells and choose a suitable date style.

Does MAX include hidden or filtered-out rows?
Yes. MAX evaluates the referenced range, including rows hidden manually or by a filter. If you need a visible-rows-only answer, use an approach designed for filtered data rather than expecting MAX to change with the filter.

Why does MAX show the right day but the wrong time?
Excel stores times as fractions of a day, and MAX returns the largest full date-and-time value. Apply a format that includes hours and minutes to see whether one entry is later than the others.

Can MAX find the latest date for one person or category?
Yes. In Microsoft 365 or Excel 2019 and later, MAXIFS returns the maximum date that matches a condition. Ensure the date cells are numeric and that the criteria range and date range cover corresponding rows.

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.