Excel Max Date in Range: Formula & Examples

Formulas & functions · Updated November 22, 2026

The quickest way to find the maximum date in an Excel range is with the MAX function: =MAX(A2:A100) returns the latest date (dates are stored as numbers). For a single condition, use MAXIFS (Excel 2019+ / Microsoft 365); for multiple conditions or older versions, use an array formula with MAX(IF(...)).

Why Dates Are Numbers in Excel

Excel stores every date as a serial number: January 1, 1900 is 1, January 2, 1900 is 2, and so on. The date you see in a cell is just a number with a date number format applied. Because of this, functions that find the largest number—like MAX—automatically find the latest date. This also means you can subtract dates, add days, and compare them with normal operators.

When you enter a date, Excel converts it to its serial number and applies a date format. If you see a large number like 45292, the cell is formatted as General—change it to a date format (Home > Number Format > Short Date) to display it correctly.

Using MAX for the Latest Date

The simplest formula assumes your dates are in a contiguous range, for example A2:A20. Enter:

=MAX(A2:A20)

This returns the largest serial number, which Excel displays as the latest date. If your range contains blank cells, MAX ignores them. If it contains text or errors, MAX ignores text but returns an error if any cell has an error value.

Example: A2:A5 contains 2024-01-15, 2024-03-22, 2024-02-10, 2024-04-01. =MAX(A2:A5) returns 2024-04-01 (serial 45383).

Finding the Max Date with Conditions (MAXIFS)

Often you need the latest date for a specific item, like the last order date for a particular product. MAXIFS handles this easily. It requires Excel 2019 or Microsoft 365. Syntax:

=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Assume dates in B2:B20 and product names in A2:A20. To find the latest date for “Widget”:

=MAXIFS(B2:B20, A2:A20, "Widget")

This returns the maximum date in B where the corresponding A cell equals “Widget”. You can add more pairs for multiple conditions, e.g., also matching a region in C2:C20:

=MAXIFS(B2:B20, A2:A20, "Widget", C2:C20, "North")

Criteria can be cell references: =MAXIFS(B2:B20, A2:A20, E1).

Using MAX with IF for Older Excel Versions

If you have Excel 2016 or earlier, MAXIFS isn’t available. Use an array formula: MAX(IF(...)). With the same layout (dates in B2:B20, products in A2:A20), enter:

=MAX(IF(A2:A20="Widget", B2:B20))

In older Excel, you must press Ctrl+Shift+Enter to enter it as an array formula (curly braces appear). In Excel 365 and 2021, you can press Enter normally because dynamic arrays are supported. This formula evaluates the condition for each row and returns the maximum of the dates where true.

For multiple conditions, multiply conditions inside the IF: =MAX(IF((A2:A20="Widget")*(C2:C20="North"), B2:B20)). Use * for AND logic.

Common Problems and How to Fix Them

  • Formula returns 0 or a weird number: The cell is formatted as General. Change to a date format.
  • Formula returns a date from a hidden row or wrong column: Double-check the range references.
  • MAXIFS returns 0 when there are no matches: That’s by design—0 is the smallest serial number (January 0, 1900). Use IFERROR to show a blank: =IFERROR(MAXIFS(...), "").
  • Dates stored as text: If your dates are left-aligned, they’re text. Use DATEVALUE to convert: =MAX(DATEVALUE(A2:A20)) as an array formula, or use Text to Columns (Data > Text to Columns > Next > Next > Date: MDY).
  • MAX ignores dates after 9999? Excel supports dates up to 12/31/9999. Beyond that, it’s text.

Alternatives: AGGREGATE and Array Formulas

Another option is AGGREGATE, which can ignore errors and works in all versions. To get the max date ignoring errors:

=AGGREGATE(4, 6, B2:B20)

The 4 means MAX, 6 means ignore error values. This is useful when your range has #N/A from VLOOKUP.

For a single condition without array entry, you could use SUMPRODUCT with MAX? No—SUMPRODUCT can’t directly return max. Stick with MAXIFS or array formulas.

Finding the Max Date with a PivotTable

If you prefer a visual method, a PivotTable can show the latest date per group without formulas. Select your data, go to Insert > PivotTable. Drag the date field to Values, then change the value field settings to Max (right-click > Summarize Values By > Max). Drag the grouping field to Rows. This updates automatically when you refresh.

Frequently Asked Questions

How do I find the max date in a column ignoring blanks?

Just use =MAX(A:A)—it ignores blank cells automatically. If you have text or errors, use =AGGREGATE(4,6,A:A) to ignore errors too.

Can I use MAXIFS with a date condition like “before 2024”?

Yes. Use a date criterion in quotes: =MAXIFS(B2:B20, A2:A20, "<"&DATE(2024,1,1)). The DATE function returns a serial number that Excel compares correctly.

Why does my MAX formula return a date from 1900?

That's likely because your range includes a cell with the value 0 or a blank that's formatted as a date (shows as 1/0/1900). Check for hidden zeros. Use =MAX(IF(A2:A20<>0, A2:A20)) as an array formula to exclude zeros.

What's the difference between MAX and LARGE?

MAX returns the single largest value. LARGE returns the nth largest: =LARGE(A2:A20, 1) is the same as MAX, but =LARGE(A2:A20, 2) gives the second latest date. Use LARGE for top-N analysis.

How do I find the max date for each unique item without a PivotTable?

Use the UNIQUE function (Excel 365) to list items, then MAXIFS for each. For example, in D2: =UNIQUE(A2:A20), then in E2: =MAXIFS(B2:B20, A2:A20, D2) and drag down. This updates automatically with dynamic arrays.

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.