Excel MIN and MAX Dates: Find the Earliest and Latest

How-to basics · Updated November 19, 2026

With dates in A2:A100, use =MIN(A2:A100) to return the earliest date and =MAX(A2:A100) to return the latest. If the results look like numbers, format the result cells as dates; Excel stores dates as numbers underneath.

How Excel stores dates

Excel represents dates with serial numbers, so it can compare them and perform date arithmetic. In the default date system used by most modern Windows workbooks, January 1, 1900 is serial number 1, and later dates have larger serial values. A date that looks like March 14, 2025, is therefore a number to Excel, even though the cell displays it as a calendar date.

This is why MIN and MAX work for dates without special date-specific functions: the smallest serial number is the earliest date, and the largest is the latest. The cell’s display format does not change the underlying value. If a result is displayed as 45730, for example, it may be a valid date serial shown with a General or Number format rather than a date format.

Excel dates can also include times. A time is stored as a fraction of a day, so 12:00 noon is one half of a day. When timestamps are present, MIN and MAX compare the full date-and-time values, not just the calendar dates. That distinction matters when two records fall on the same day.

Find the earliest and latest date in a range

For a simple list, select two empty cells and enter one formula for each result. With dates in A2:A6, use =MIN(A2:A6) for the earliest and =MAX(A2:A6) for the latest. If the cells contain January 5, March 14, March 14, and July 22, 2025, among other entries, the results are January 5 and July 22, 2025.

The formula can refer to a whole column, such as =MIN(A:A), but a defined data range is often easier to audit and can avoid scanning unrelated content. If the list is in an Excel Table named Dates with a column named EventDate, a structured reference can expand as rows are added. With EventDate as the table column, use =MIN(Dates[EventDate]) and =MAX(Dates[EventDate]). Table names and column names must match the workbook.

If the range has no valid numeric date values, MIN returns 0 and MAX returns 0. If formatted as a date, zero may display as a date near the beginning of Excel’s date system, which is not a meaningful earliest or latest date for your records. Check that the range actually contains dates before interpreting that result.

Format the result as a readable date

To display a serial result as a date in Excel for Microsoft 365 on Windows, select the result cell, then choose Home > Number Format and select Short Date or Long Date. You can also press Ctrl+1 to open Format Cells, choose the Number tab, and select Date. On a Mac, use Command+1 to open Format Cells; the number-format controls are also available from the Home tab. Excel for the web provides date formats through the Number Format menu on Home.

Choose a format that makes the year unambiguous when the workbook may be shared across regions. For example, 03/04/2025 can mean March 4 or April 3 depending on regional conventions. A format such as 4-Mar-2025 or 2025-03-04 is easier to interpret consistently. Formatting changes only how the value appears; it does not change the date or the MIN/MAX calculation.

To include a time in the result, choose a custom format such as m/d/yyyy h:mm AM/PM in Format Cells. This is useful when the range contains timestamps and you need to see which record is truly first or last. If your goal is to find the first or last calendar day only, the time component may not matter, but it can affect which row is returned by a lookup based on the exact minimum or maximum.

Find dates that meet a condition

MIN and MAX evaluate all numeric values in their referenced range; they do not filter records by status, person, or category. In Microsoft 365, MINIFS and MAXIFS return the smallest or largest value that meets one or more criteria. They are useful for questions such as the earliest open order date or latest date for a particular customer.

With dates in C2:C100 and order statuses in B2:B100, use =MINIFS(C2:C100,B2:B100,"Open") for the earliest date among rows marked Open, and =MAXIFS(C2:C100,B2:B100,"Open") for the latest. These functions require the criteria range and the date range to have matching dimensions. They are available in Microsoft 365 and recent perpetual Excel versions, including Excel 2019 and later.

For a date window, suppose C2:C100 contains dates and E2 and F2 contain the inclusive start and end dates. With those cells as the boundaries, use =MINIFS(C2:C100,C2:C100,">="&E2,C2:C100,"<="&F2) to find the earliest date in the window, and =MAXIFS(C2:C100,C2:C100,">="&E2,C2:C100,"<="&F2) to find the latest. The criteria join comparison operators to the boundary-cell values; E2 and F2 need to be real Excel dates, not date-looking text.

MINIFS and MAXIFS return 0 when no value meets the criteria. That can resemble a date when the result is formatted as one. If this is possible, display a message instead of a misleading zero. With dates in C2:C100, statuses in B2:B100, and the criterion Open, use =IF(COUNTIFS(B2:B100,"Open",C2:C100,">0")=0,"No matching dates",MINIFS(C2:C100,B2:B100,"Open")). The count checks whether a qualifying numeric date exists before showing the minimum.

Handle dates that include times

When your data includes timestamps, a condition ending at midnight can unintentionally exclude records later on the final day. For example, if F2 contains June 30, 2025 at midnight, a condition of less than or equal to F2 includes only values up to that instant, not timestamps from later on June 30.

A reliable way to include every time on an end date is to use an exclusive upper boundary: less than the day after the end date. With timestamps in C2:C100, a start date in E2, and an end date in F2, use =MINIFS(C2:C100,C2:C100,">="&E2,C2:C100,"<"&(F2+1)) for the earliest timestamp in the inclusive calendar-date range. For the latest timestamp, replace MINIFS with MAXIFS. Adding 1 advances the date by one day; the strict less-than condition includes times before midnight at the start of the following day.

If you only need the calendar date and do not need the time, you can format the result to hide the time. Remember that hiding the time does not remove it. Two results that appear to be the same date may still have different underlying timestamps, which can affect sorting, comparisons, and later calculations.

Common problems and how to fix them

A date that is stored as text may look correct but be ignored by MIN and MAX. This can happen after copying data from a website, importing a text file, or using a format Excel does not recognize under the current regional settings. A quick clue is that the value may be left-aligned by default, or changing its number format does not turn it into a date.

  • Check whether the value is numeric. Select a suspected date and look at the formula bar and number format. You can test a cell with ISNUMBER; dates stored as real Excel dates return TRUE.
  • Convert text dates. Depending on the source and locale, Data > Text to Columns can convert recognizable date text. For a small list, re-entering the date using a format Excel recognizes can also help. Verify the converted values before relying on the result.
  • Check for errors. An error value in the referenced range can cause a MIN or MAX formula to return an error. Resolve the source error or deliberately exclude invalid rows rather than assuming Excel will skip them.
  • Check the range. A formula may omit new rows or include unrelated numbers. Confirm the first and last cell references, or use an Excel Table so its data column can grow with new records.

MIN and MAX generally ignore blank cells and text within a referenced range, but they do consider numeric values. A stray number in a date column can therefore become the apparent earliest or latest value. Conversely, a date-looking text entry will not participate as a date. Cleaning the column and checking the results against the source rows is safer than relying on appearance alone.

Return the row associated with a date

MIN and MAX return a date value, not the other information from the row where that date appears. If you need the associated project name or customer, use the minimum or maximum as a lookup value. With dates in A2:A100 and project names in B2:B100, Microsoft 365 can return the name corresponding to the latest date using =XLOOKUP(MAX(A2:A100),A2:A100,B2:B100,"Not found"). XLOOKUP returns the first matching row if the maximum date occurs more than once.

When duplicate latest dates are possible, decide which matching row you want, or return all matching records with a suitable filtering approach. The same issue applies to the earliest date. Also note that this lookup compares the full underlying value: if two timestamps differ by minutes, only the row matching the exact maximum timestamp is selected.

When to use a different approach

For a single earliest or latest value, MIN and MAX are usually the clearest tools. Use MINIFS and MAXIFS when a calculation needs criteria. If the immediate goal is to inspect or sort records rather than produce a reusable result, sorting the date column from oldest to newest or newest to oldest can make the relevant row easy to review. Sorting changes the order of records, so sort the entire table, not just the date column, to keep each row’s values together.

A PivotTable can summarize dates by a grouping field and show a minimum or maximum value for each group. This is useful for comparing many customers, teams, or categories, but it requires setting up and refreshing a summary rather than entering one formula. If you need a dynamic list of rows within a date range, Excel’s FILTER function in Microsoft 365 can return matching records; it is a different task from calculating only one boundary date.

Frequently Asked Questions

What formula finds the oldest date in Excel?
Use MIN on the cells containing valid Excel dates. With dates in A2:A100, =MIN(A2:A100) returns the earliest date. Format the result cell as a date if Excel displays a serial number.

What formula finds the most recent date in Excel?
Use MAX on the date range. With dates in A2:A100, =MAX(A2:A100) returns the latest date or timestamp in that range.

Why does Excel show a number instead of a date?
Excel stores dates as serial numbers. Select the result and apply Short Date or another date format from Home > Number Format. The number is not necessarily an error.

Why does MIN or MAX return zero?
The range may contain no numeric dates, or a MINIFS/MAXIFS calculation may have no qualifying rows. Check for text-formatted dates and criteria that do not match. A zero formatted as a date is not a valid answer to your data question.

Can MIN and MAX find dates for one person or status?
Yes. Use MINIFS or MAXIFS and provide a criteria range and criterion, such as a status column and the value Open. Make sure the ranges line up and that the date values are stored as real Excel dates.

Do these formulas include times in the comparison?
Yes. Excel compares the full date-and-time serial value. MIN returns the earliest timestamp and MAX returns the latest; a date-only display format can hide the time without changing the comparison.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.