Find the Max Date in Excel: Formula, Filters, and Fixes

Formulas & functions · Updated November 20, 2026

To find the latest date in Excel, put your dates in a range such as B2:B20, then enter =MAX(B2:B20) in an empty cell. Excel returns the largest date value in that range; if the result looks like a number, format the result cell as a date.

Why MAX finds the latest date

Excel stores recognized dates as numbers, called serial values. Later dates have larger serial values, so the MAX function—which returns the largest number in a range—also finds the latest date. You do not need to sort the list first, and the dates do not have to be in chronological order.

For example, if B2:B6 contains June 1, June 12, May 29, June 8, and June 4, 2025, the formula returns June 12, 2025. MAX ignores empty cells and text in a referenced range, so a blank among valid dates does not prevent it from finding the latest one.

The function finds the greatest value, not necessarily the most recently entered record. If a date was typed into an old row yesterday but is earlier than the other dates, MAX still returns the later calendar date in the list. It also does not identify which row contains that date; that requires a lookup or another step.

Enter the formula and display the result as a date

With dates in cells B2:B20, enter this formula in an empty cell outside the range:

=MAX(B2:B20)

Press Enter. If those cells contain real Excel dates, the result is the latest one. If Excel displays a number such as 45820 instead of a familiar date, the calculation may still be correct: the result cell is simply using a number format.

  1. Select the cell containing the MAX formula.
  2. On the Windows ribbon, choose Home > Number Format, then choose Short Date or Long Date. The Number Format control is in the Number group.
  3. If the displayed style is not what you want, open the Format Cells dialog with Ctrl+1 and choose a date format.

Changing the number format changes how the value appears, not the underlying date. A date format also cannot repair a text value that Excel does not recognize as a date; that is a separate data issue discussed below.

Find the latest date that meets a condition

Use MAXIFS when you want the latest date for a particular category, person, project, or other criterion. For example, suppose column A contains a region and C contains the corresponding date. With region names in A2:A7 and real dates in C2:C7, enter:

=MAXIFS(C2:C7,A2:A7,"North")

If the North rows contain April 2, April 18, and April 11, 2025, the formula returns April 18, 2025. Format the formula cell as a date if Excel shows a serial number. MAXIFS is available in Microsoft 365 and Excel 2019 or later; it is not available in some older Excel releases.

The date range and criteria range must correspond row by row and have the same dimensions. In the example, C2:C7 supplies the dates to compare, while A2:A7 tells Excel which rows qualify. For a criterion stored in a cell, such as E2, replace the quoted word with E2. That makes it easier to reuse the formula for another region without editing the formula text.

Get the maximum from filtered rows only

A regular MAX formula considers every value in its range, including rows hidden by an AutoFilter. That can surprise users who filter a table to one department and expect the result to reflect only the visible records. For a maximum that responds to filters, use SUBTOTAL.

With dates in B2:B20, enter this formula in a cell outside the filtered range:

=SUBTOTAL(104,B2:B20)

The result is the latest date among the visible rows. The number 104 tells SUBTOTAL to perform the MAX operation while also excluding rows hidden manually; SUBTOTAL also excludes rows removed by a filter. By comparison, function number 4 performs MAX but does not exclude manually hidden rows. Format the result as a date if needed.

Keep the summary cell outside the rows being filtered where practical. SUBTOTAL is designed to work with filtered lists, but placing a summary inside the list can make it harder to interpret or maintain.

Check whether the dates are stored as text

A cell can look like a date while containing text instead of a date value. This often happens when data is imported from a website, a text file, or another system, or when the date order is ambiguous. MAX does not treat ordinary text in a referenced range as a date, so it can skip those entries and return an earlier date—or return 0 if it finds no numeric dates.

To check a suspect cell, select it and look at the formula bar and alignment. Text is often left-aligned by default, while numbers and recognized dates are often right-aligned, although alignment may have been changed manually. You can also temporarily change the cell format to General: a genuine date displays its serial value, while text usually remains in its original written form. Neither visual check is conclusive on its own, so compare several cells or test a copy of the data.

For a column of consistently formatted text dates, select the cells and choose Data > Text to Columns. Follow the wizard, select Date, choose the order that matches the source data (for example, MDY or DMY), and finish the conversion. Check a few results before using MAX, especially when dates such as 03/04/2025 could mean March 4 or April 3. If the source uses mixed formats, clean or convert those values carefully rather than applying one date order to the entire column.

Understand dates that include times

Excel dates can include a time of day as a fractional part of the date serial. As a result, MAX returns the latest date-and-time value, not just the latest calendar day. If two records are on June 12 and one is at 9:00 a.m. while the other is at 4:00 p.m., MAX returns the 4:00 p.m. entry. A date-only format hides the time, which can make two different results appear identical.

If you need the calendar date of the latest timestamp and do not need its time, with timestamps in B2:B20 enter:

=INT(MAX(B2:B20))

For example, if the maximum value represents June 12, 2025 at 4:00 p.m., INT removes the fractional time and the result displays as June 12, 2025 when formatted as a date. If you need the time as well, use the regular MAX formula and apply a date-and-time format instead.

Common problems and what to check

  • The result is a number: Apply a date format to the formula cell. Excel stores dates numerically, and a general number format reveals the serial value.
  • The result is 0: Check that the source contains at least one recognized numeric date. The cells may be blank, text, or formulas returning text.
  • The answer is an earlier date than expected: Inspect later-looking entries for text storage, spelling or import issues, and verify the range includes every relevant row.
  • The answer ignores a filter: MAX includes filtered-out rows. Use SUBTOTAL with function number 104 when the result should reflect visible rows.
  • The displayed date looks wrong: Check the source’s day-month order and the workbook’s regional interpretation. Changing a format changes appearance; it does not reinterpret an incorrectly converted text date.

Also make sure the formula range does not accidentally include unrelated dates, such as a heading converted to a date or a separate notes section. When a list grows, an Excel Table can make ranges easier to maintain: formulas that refer to a table column can include new table rows as the table expands. Inspect the formula after adding data to confirm it covers the intended records.

Frequently Asked Questions

What is the formula for the latest date in Excel?
With dates in B2:B20, use =MAX(B2:B20). Format the result cell as a date if Excel displays a serial number.

Does MAX ignore blank cells?
Yes. MAX ignores blank cells in a referenced range and compares the numeric values it finds. It also ignores ordinary text, which is why date-like text may need converting first.

How do I find the latest date for one person or category?
Use MAXIFS with the date range and a matching criteria range. For example, if A2:A7 contains names and C2:C7 contains dates, =MAXIFS(C2:C7,A2:A7,"Maya") returns the latest date on rows labeled Maya. MAXIFS requires Excel 2019 or later, including Microsoft 365.

Why does MAX return a number instead of a date?
Excel dates are stored as serial numbers. Select the result cell and choose Home > Number Format > Short Date, or select another date format in Format Cells.

Can MAX find the latest date in visible filtered rows?
Not by itself: MAX still includes filtered-out rows. Use =SUBTOTAL(104,B2:B20) for the maximum among visible rows, including when rows are hidden manually.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.