Excel Find Earliest Date Based on Criteria
To find the earliest date in Excel based on one or more criteria, use an array formula with MIN and IF, or the newer MINIFS function. This article explains both methods, shows step-by-step examples, and covers common pitfalls like text-formatted dates and blank cells.
Why You Need a Criteria-Based Date Lookup
A simple MIN function returns the earliest date in a whole column. But real data usually has categories: sales by region, tasks by owner, orders by product. You need the earliest date for a specific group, not the entire list. Excel offers two main approaches: MINIFS (available in Excel 2019 and Microsoft 365) and the older array formula with MIN and IF (works in all versions). Both give the same result; the choice depends on your Excel version and comfort with array formulas.
Using MINIFS (Simplest Method)
MINIFS works like SUMIFS but returns the minimum value. The syntax is:
MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
- min_range – the range containing the dates you want to evaluate.
- criteria_range1 – the range to check for the first condition.
- criteria1 – the value or expression to match (e.g., “East”, “Project A”, or “>=”&DATE(2024,1,1)).
Example: to find the earliest order date for customer “Acme Corp” with dates in B2:B100 and customers in A2:A100, enter:
=MINIFS(B2:B100, A2:A100, "Acme Corp")
The result is a date serial number; format the cell as a date to display it properly. You can add more criteria pairs: for example, also require the region to be “East” by adding C2:C100, “East”.
Array Formula with MIN and IF (Older Versions)
If you don’t have MINIFS, use an array formula. Enter the following and press Ctrl+Shift+Enter (not just Enter) in Excel 2019 and earlier. In Microsoft 365, it works with a normal Enter because dynamic arrays are automatic.
=MIN(IF(A2:A100="Acme Corp", B2:B100))
The IF function returns an array of dates where the condition is true, and FALSE for all other rows. MIN ignores FALSE values. If no rows match, the formula returns 0 (which displays as 1900-01-00 or 1900-01-01 depending on your system). To avoid that, wrap it in IFERROR:
=IFERROR(MIN(IF(A2:A100="Acme Corp", B2:B100)), "No match")
Remember to confirm with Ctrl+Shift+Enter in older Excel, otherwise you get a #VALUE! error or a wrong result.
Adding Multiple Criteria
Both methods extend to multiple conditions. For MINIFS, just add more range/criteria pairs:
=MINIFS(B2:B100, A2:A100, "Acme Corp", C2:C100, "East")
For the array formula, multiply the conditions inside the IF:
=MIN(IF((A2:A100="Acme Corp")*(C2:C100="East"), B2:B100))
The multiplication acts like an AND operator: 1*1=1 (true), any 0 makes it false. For OR logic, use addition with a check that the sum is greater than 0, but that gets more complex and is rarely needed for date lookups.
Common Pitfalls and How to Avoid Them
- Dates stored as text – If your “dates” are actually text strings (e.g., “2024-01-15” as text), MIN/MINIFS will not treat them as dates. Check with =ISTEXT(A2). Convert text to real dates using DATEVALUE or Text to Columns.
- Blank cells or zeros – MIN ignores blank cells, but it does NOT ignore zero. If your date column contains 0 (often from a bad formula), the earliest date will show as 1900-01-00. Use =MINIFS(B2:B100, A2:A100, “Acme Corp”, B2:B100, “>0”) to exclude zeros.
- Hidden rows or filtered data – MINIFS and MIN/IF ignore hidden rows only if you use SUBTOTAL or AGGREGATE. They do not respect filters. If you need the earliest date among visible rows, use AGGREGATE(15, 7, range, 1) with criteria, which is more advanced.
- Array formula not entered correctly – In older Excel, if you press Enter instead of Ctrl+Shift+Enter, you get #VALUE! or a single wrong value. Look for the curly braces {} in the formula bar; if they are missing, re-enter with Ctrl+Shift+Enter.
- Criteria with wildcards – MINIFS supports wildcards (* and ?) for text criteria, but MIN/IF does not. If you need partial matching, use MINIFS or combine with ISNUMBER(SEARCH()).
When to Use Each Method
Choose MINIFS if you have Excel 2019 or Microsoft 365 – it is simpler, faster, and less error-prone. Use the array formula only if you are on an older version (Excel 2016 or earlier) and cannot upgrade. The array formula also gives you more flexibility for complex logic (like OR conditions) that MINIFS cannot handle directly. For very large datasets (tens of thousands of rows), MINIFS will calculate faster than an array formula because it is optimized internally.
Alternative: PivotTable
If you need to see the earliest date for every category at once, a PivotTable is a quick alternative. Drag the category field to Rows, drag the date field to Values, and set the value field setting to Min. This shows the earliest date for each category without writing any formulas. However, a PivotTable does not update automatically when new data is added; you must refresh it. It also cannot easily handle multiple criteria that change based on cell references unless you add helper columns.
Frequently Asked Questions
Can I use MINIFS with a date condition like “before 2024-01-01”?
Yes. Use a criteria like “<"&DATE(2024,1,1) or a cell reference: "<"&E1 where E1 contains a date. For example: =MINIFS(B2:B100, A2:A100, "Acme Corp", B2:B100, "<"&DATE(2024,1,1)).
Why does my MINIFS return 0 or 1900-01-00?
That usually means no rows meet the criteria, or the min_range contains zeros. If you have zeros, add a criteria to exclude them: B2:B100, “>0”. If no rows match, use IFERROR to show a custom message.
How do I find the earliest date excluding weekends?
MINIFS cannot directly exclude weekends. You need a helper column that flags weekday dates with =WEEKDAY(date,2)<6, then use that column as a criteria. Or use an array formula with the WORKDAY function, but that is more complex.
Can I use MINIFS with a cell reference for the criteria?
Absolutely. Instead of typing “Acme Corp”, use a cell reference like =MINIFS(B2:B100, A2:A100, D1). This makes it easy to change the criteria without editing the formula.
What if my dates are in a table with structured references?
MINIFS works with structured references too. For example, =MINIFS(Table1[OrderDate], Table1[Customer], “Acme Corp”). The same syntax applies, and it automatically adjusts when you add rows to the table.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.