Excel MAX Function for Dates: Find the Latest Date

Formulas & functions · Updated November 19, 2026

To find the latest date in Excel, use =MAX(A2:A20), replacing the range with your date cells. Excel stores real dates as serial numbers, so MAX returns the largest date value—which is the latest date chronologically.

What MAX does with dates

Excel dates look like calendar values, but internally a valid date is a number that counts days from a starting point. In the standard 1900 date system used by most Windows workbooks, later dates have larger serial numbers. MAX compares those numbers and returns the greatest one. You can use it for deadlines, order dates, inspection dates, milestones, or any other date column.

For example, suppose A2:A6 contains January 15, 2024; November 2, 2024; December 31, 2023; February 10, 2025; and a blank. With dates in A2:A6, the formula =MAX(A2:A6) returns February 10, 2025. Blank cells are ignored. The result may initially appear as a number rather than a date; that is a display-format issue, not a different answer.

MAX can also compare date values supplied as separate arguments, but a range is usually clearer and easier to maintain. It does not identify the row or event that owns the latest date; it only returns the maximum value. If you need the associated name or record as well, use a lookup approach after finding the date.

Enter the formula and format its result

In Excel for Microsoft 365 on Windows, select the cell for the answer, type the formula, and press Enter. If your date entries are in A2:A20, enter =MAX(A2:A20). The formula updates when a date in that range changes, provided the cell contains a real Excel date and the calculation setting is automatic.

If the result appears as a value such as 46000, select the result cell and choose Home > Number Format, then select Short Date or Long Date. You can also use the keyboard shortcut Ctrl+Shift+# on Windows to apply a date format. On Mac, select a date format from the Home tab’s Number Format controls; the Windows shortcut is not the same on Mac. Formatting changes how the number appears, not the underlying value.

It is often useful to format the source date column as dates too, so you can spot inconsistent entries. Use Home > Number Format > Short Date. A cell’s appearance alone does not prove that it contains a valid date: text can be made to look like a date, yet still be ignored by MAX.

Dates with times and date-only results

Excel can store a time as a decimal fraction of a day alongside the date. For example, two entries may both display as March 8, while one is 9:00 AM and the other is 4:30 PM. MAX compares the full values, so it returns the later timestamp. If the result is formatted to show only the date, the time is hidden; it has not been removed.

This distinction matters when a column records appointments, deliveries, or system events. If you want the latest moment, use MAX as usual and apply a date-and-time format to the result. If you only care about the calendar day, a date-only display is generally sufficient, but remember that two records on the same day may have different underlying times.

To show the latest date without its time component, use INT on the maximum value. With date-and-time values in A2:A20, the formula =INT(MAX(A2:A20)) returns the serial number for the latest calendar day at midnight. Format the result as a date. This removes the fractional time from the returned value; it does not change the source cells.

Find the latest date that meets a condition

When the question is “What is the latest date for this customer, team, or status?”, use MAXIFS rather than MAX. MAXIFS returns the maximum from one range while applying one or more criteria to corresponding ranges. It is available in Microsoft 365 and Excel 2019 or later, including Excel 2021 and Excel 2024.

Suppose dates are in A2:A20 and region names are in B2:B20. With those ranges and the region North in the criteria, enter =MAXIFS(A2:A20,B2:B20,"North"). If the North rows contain January 3, April 18, and February 12, 2025, the result is April 18, 2025. The date range and criteria range should cover matching rows and have the same dimensions.

You can also set date boundaries as criteria. With dates in A2:A100, this formula finds the latest date in calendar year 2025: =MAXIFS(A2:A100,A2:A100,">="&DATE(2025,1,1),A2:A100,"<"&DATE(2026,1,1)). The inclusive lower boundary and exclusive first day of the next year include every time on dates in 2025, even if the cells contain timestamps. The result is the latest qualifying date and time, so format it as needed.

If no row matches the criteria, MAXIFS returns 0. A cell formatted as a date may make that zero look like an unexpected early date. Check that the criterion text matches the data, that the date boundaries are correct, and that the criteria ranges line up with the date range.

When Excel does not recognize a date

A common reason for an unexpected MAX result is that some apparent dates are actually text. This often happens after copying data from a website, importing a CSV, or combining files with different regional date conventions. MAX ignores text in a referenced range, so it may return the latest date among only the entries Excel recognizes as numeric dates.

Check a suspicious cell by changing its number format to General. A real date usually displays as a serial number under General; text typically remains text. You can also test a cell with ISNUMBER. With a suspected date in A2, =ISNUMBER(A2) returns TRUE for a numeric date value and FALSE for text. The test does not itself convert anything.

For a small number of clearly recognizable text dates, DATEVALUE can convert a date string to an Excel date serial. With a text date in A2, enter =DATEVALUE(A2) in another cell, then format the result as a date. DATEVALUE depends on how Excel interprets the string under your regional settings. A value such as 03/04/2025 is ambiguous across date conventions, so confirm whether it means March 4 or April 3 before relying on a conversion. For a larger import, use Data > Text to Columns and select the appropriate date order when the wizard offers a date format option, or correct the source data and import it consistently.

Other issues can also affect the result. An error value in the referenced range can make MAX return an error, so inspect the source cells if you see one. A genuine date serial of zero or dates outside the intended period can also skew a result. Filters do not automatically limit an ordinary MAX formula to visible rows: MAX evaluates the referenced range, including rows hidden by a filter.

MAX compared with MAXIFS and filtered results

Choose MAX when you want the latest value in the whole range. Choose MAXIFS when you need the latest value that satisfies criteria, such as a particular region or a date interval. MAXIFS is more direct than building a conditional calculation, and its separate criteria arguments make the logic easier to inspect.

Neither formula automatically respects a worksheet filter. If you filter a table to show one customer and use MAX on its date column, hidden rows can still contribute to the answer. If the desired result depends on visible rows only, make that requirement explicit and choose a method designed for the workbook’s Excel version and data layout. Do not assume that hiding rows changes the values a standard MAX formula evaluates.

For a one-time answer, sorting the date column from newest to oldest can help you inspect the latest entry, but it changes the row order and can be less useful than a formula in a report. A MAX formula keeps a summary cell current and avoids relying on a manual sort. If you need both the latest date and its matching record, MAX alone is not enough; ties also need consideration because more than one row may share that date.

Practical checks for reliable results

Before trusting a latest-date calculation, verify the source and the question the formula is meant to answer. A few quick checks can prevent common reporting mistakes:

  • Confirm the range includes all intended rows and excludes headers or unrelated values.
  • Check that imported dates are numeric Excel dates, not text that merely looks like a date.
  • Decide whether timestamps matter. MAX includes the time fraction even when the cells display only a date.
  • Check for errors, accidental zero values, or dates outside the reporting period.
  • Use MAXIFS when you need conditions, and verify that each criteria range aligns row by row with the date range.
  • Remember that filtering or hiding rows does not make MAX ignore them.

Excel workbooks can use different date systems. Most Windows workbooks use the 1900 system, while some older workbooks—often encountered in Mac-related workflows—may use the 1904 system. This usually does not affect MAX within one workbook: it still compares the date numbers stored there. It can matter when values are copied between workbooks using different systems, so if dates shift by several years after a transfer, check the workbook date-system setting and the import or copy method.

Frequently Asked Questions

Does MAX work with dates in Excel?
Yes. Excel stores valid dates as numbers, and MAX returns the greatest number in the range. For a date range in A2:A20, use =MAX(A2:A20) and format the result as a date if it appears as a serial number.

How do I get the latest date for one person or category?
Use MAXIFS with the date range, the corresponding category range, and the category to match. Make sure both ranges cover the same rows. MAXIFS requires Excel 2019 or later, including Microsoft 365.

Why does MAX return a number instead of a date?
The answer is usually a date serial displayed with General or Number formatting. Apply Short Date or another date format to the result cell. Changing the format changes its appearance, not the value.

Why is MAX ignoring dates I can see in the cells?
Those entries may be text rather than real Excel dates. Test a cell with ISNUMBER or change its format to General to investigate. Convert text dates only after checking that their day, month, and year order is being interpreted correctly.

Does MAX ignore filtered-out rows?
No. MAX evaluates the cells in its referenced range even when worksheet filters hide some rows. Use a method specifically designed for visible-only calculations if that is the intended result.

Bottom line

For the latest date in a range, MAX is usually all you need: point it at the date cells, then format the result as a date. Use MAXIFS for a latest-date answer with conditions, and check for text dates, timestamps, errors, and hidden rows when the result does not match what you expect.

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.