The simplest Excel formula to find the minimum (earliest) date in a range is =MIN(A2:A100), assuming your dates are in cells A2 through A100. This returns the smallest date value, which Excel stores as a serial number. For most date columns, that is all you need.
How Excel Stores Dates
Excel treats dates as sequential serial numbers. January 1, 1900 is serial number 1, January 2, 1900 is 2, and so on. For example, July 4, 2024 is serial number 45,477. Because dates are numbers, the MIN function—which returns the smallest numeric value—naturally returns the earliest date. This is why you can use MIN directly on a date column without any special date function.
Using MIN with a Date Range
Suppose you have a list of order dates in cells B2:B50. To get the earliest order date, enter:
=MIN(B2:B50)
If the result appears as a number (like 45000), the cell is formatted as General. To display it as a date, select the cell, press Ctrl+1 (or Cmd+1 on Mac), choose Date under the Number tab, and pick a format. On Excel for the web, the same formatting steps work from the Home tab > Number Format dropdown.
Finding the Earliest Date with a Condition
Often you need the earliest date that meets a condition, such as the first sale after a certain product. Use the MINIFS function (available in Excel 2019, Excel 2021, and Microsoft 365). For example, with product names in A2:A50 and dates in B2:B50, to find the earliest date for “Widget”:
=MINIFS(B2:B50, A2:A50, "Widget")
This returns the smallest date in B2:B50 where the corresponding cell in A2:A50 equals “Widget”. If no rows match, MINIFS returns 0, which displays as January 0, 1900—a common pitfall. To avoid that, wrap it with IFERROR:
=IFERROR(MINIFS(B2:B50, A2:A50, "Widget"), "No match")
Earliest Date with Multiple Criteria
For multiple conditions, add more range/criteria pairs. Say you have region in A2:A50, product in B2:B50, and dates in C2:C50. To find the earliest date for “West” region and “Widget”:
=MINIFS(C2:C50, A2:A50, "West", B2:B50, "Widget")
MINIFS works the same in Excel for Mac and Excel for the web. In older versions without MINIFS, you would need an array formula like =MIN(IF((A2:A50="West")*(B2:B50="Widget"), C2:C50)) entered with Ctrl+Shift+Enter, but MINIFS is simpler and recommended.
Common Problems and Solutions
- Dates stored as text: If your dates are left-aligned text, MIN ignores them. Convert to real dates using DATEVALUE or the Text to Columns wizard (Data > Text to Columns > Delimited > Next > Date: MDY).
- Blank cells: MIN ignores empty cells, so blanks do not affect the result. However, if a cell contains 0 (a number), that will be returned as the minimum, which displays as 1900-01-00. Remove or filter such cells.
- Formulas returning dates: If your range contains formulas like
=TODAY(), MIN works fine because the formula returns a numeric date. - Formatting: If the result shows a serial number, apply a date number format.
Alternatives: MINA, SMALL, and Array Formulas
MINA includes logical values and text representations of numbers in the calculation, but for dates it behaves like MIN. SMALL can return the nth smallest date: =SMALL(A2:A100, 1) returns the smallest, =SMALL(A2:A100, 2) the second smallest, and so on. This is useful for top-3 earliest dates. For a dynamic array of the earliest dates per category, you could use SORT and FILTER (Excel 2021 or Microsoft 365), but MINIFS is the direct answer for a single earliest date.
Frequently Asked Questions
Why does my MIN formula return 0 or a weird date?
If your range includes a cell with 0 (a number, not a blank), MIN returns 0, which displays as 1900-01-00. Check for cells that contain 0 or are formatted as text. Also, if you used MINIFS and no criteria match, it returns 0. Use IFERROR to catch that.
How do I find the earliest date ignoring blanks?
MIN already ignores blank cells. If you have cells with 0, you can use an array formula like =MIN(IF(A2:A100>0, A2:A100)) entered with Ctrl+Shift+Enter (or just Enter in Microsoft 365 if dynamic arrays are enabled). This returns the smallest positive value, which for dates means the earliest real date.
Can I use MIN on a column with dates and times?
Yes. Excel stores dates and times as a single serial number (e.g., 45500.5 for noon on a date). MIN returns the earliest date-time, which is the smallest serial number. If you only want the earliest date ignoring time, use =INT(MIN(A2:A100)) and format as a date.
What if my dates are in a non-contiguous range?
You can list multiple ranges in MIN, like =MIN(A2:A10, C2:C10). Each range is separated by a comma. MINIFS does not accept non-contiguous ranges for the min_range, but you can use a formula like =MIN(IF((A2:A10="Widget"), B2:B10), IF(C2:C10="Widget", D2:D10)) in older versions, or restructure your data.
Does MIN work with dates in a table?
Yes. If your data is in an Excel Table (Insert > Table), you can use structured references like =MIN(Table1[Order Date]). This automatically adjusts as you add rows.
Related
- Excel Data Validation Must Be a Date? No—Here’s How
- Excel Data Validation Start Date: How to Set It Up
- Excel Daily Checklist Template: How to Build One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- PowerPoint Poster Slide Size: Set the Right DimensionsTo change a PowerPoint slide to poster size in Microsoft 365 for Windows, select Design > Slide Size…
- Excel AND Function: Why It Does Not Return an ArrayExcel’s AND function returns one value, either TRUE or FALSE; it does not return a separate result for…
- Split Cells in Excel With Formulas: A Step-by-Step GuideTo split text from one Excel cell into separate columns, enter =TEXTSPLIT(A2," ") in B2 when A2 contains…
- Microsoft 365 XLOOKUP Examples for Everyday Excel TasksIn Microsoft 365, XLOOKUP finds a value in one range and returns the corresponding value from another: use…
- Number Value in Excel: Use the VALUE FunctionIn Excel, use the VALUE function to convert text that represents a number into a number Excel can…
- MATLAB split Function: Syntax and ExamplesMATLAB’s split function separates text into pieces at a delimiter and returns the pieces as a string array.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.