Excel Min Date with Criteria: Formula & Examples

Formulas & functions · Updated November 22, 2026

The fastest way to find the minimum date in Excel that meets a condition is to use the MINIFS function. For example, with dates in A2:A20 and criteria in B2:B20, the formula =MINIFS(A2:A20,B2:B20,"Completed") returns the earliest date where column B equals “Completed”. If your Excel version lacks MINIFS (pre-2019), use the array formula =MIN(IF(B2:B20="Completed",A2:A20)) entered with Ctrl+Shift+Enter.

Understanding MINIFS and Its Syntax

MINIFS is the dedicated function for finding a minimum value that satisfies one or more conditions. Its syntax is:

=MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
  • min_range: The range containing the dates (or numbers) you want to find the minimum of.
  • criteria_range1: The range to check against the first criterion.
  • criteria1: The condition, such as a text value, number, or expression like “>”&DATE(2024,1,1).
  • Additional pairs of ranges and criteria are optional; you can apply up to 126 pairs.

For example, with project names in A2:A20 and start dates in B2:B20, =MINIFS(B2:B20,A2:A20,"Project X") returns the earliest start date for Project X. The function works in Excel 2019, Excel 2021, and Microsoft 365. It is not available in Excel 2016 or earlier.

Using the Older Array Formula (Pre-2019)

If you are on an older version, the classic array formula is your fallback. With the same layout, enter:

=MIN(IF(B2:B20="Completed", A2:A20))

Because this is an array formula, you must press Ctrl+Shift+Enter (Windows) or Ctrl+Shift+Return (Mac) instead of just Enter. Excel will wrap the formula in curly braces { } automatically. If you press Enter normally, the formula may return 0 or an incorrect result. This formula works by evaluating the condition for each cell in the criteria range, returning the corresponding date if true, and FALSE otherwise. The MIN function ignores logical values, so it finds the smallest date among the true entries.

Handling Multiple Criteria

You often need to filter by more than one condition. MINIFS handles this naturally. For example, with dates in A2:A20, status in B2:B20, and region in C2:C20, find the earliest date where status is “Open” and region is “East”:

=MINIFS(A2:A20, B2:B20, "Open", C2:C20, "East")

The array formula equivalent would be:

=MIN(IF((B2:B20="Open")*(C2:C20="East"), A2:A20))

Here, the multiplication acts as an AND operator. You must still enter it with Ctrl+Shift+Enter. For an OR condition (e.g., status is “Open” or “Pending”), you can use addition inside the IF, but be careful with duplicates—use + and then check if the result is greater than 0:

=MIN(IF(((B2:B20="Open")+(B2:B20="Pending"))>0, A2:A20))

Dealing with Blank Cells and Errors

Blank cells in the min_range can cause problems. MINIFS ignores blank cells by default, but if a blank cell has a date format, it may be treated as 0, which would incorrectly become the minimum. To exclude blanks, add a criterion: =MINIFS(A2:A20, A2:A20, "<>") where "<>" means “not empty”. Similarly, if your criteria range contains errors, the function may return an error. You can wrap the formula in IFERROR to provide a friendly message: =IFERROR(MINIFS(...), "No matching dates"). For array formulas, blank cells are ignored as well, but errors in the min_range will propagate.

Comparing MINIFS with Other Approaches

Before MINIFS, users often combined MIN with IF (the array formula above) or used a pivot table. A pivot table can group data by a category and show the minimum date in the Values area, but it requires manual setup and refreshes. Another alternative is the AGGREGATE function, which can ignore errors and hidden rows. For example, =AGGREGATE(15,6,A2:A20/(B2:B20="Completed"),1) returns the smallest value after dividing each date by the condition (dates become errors when the condition is false). This works in Excel 2010 and later, but it is less intuitive. MINIFS is the cleanest and most readable option for most users.

Common Pitfalls and Troubleshooting

  • Wrong data type: Ensure your min_range contains actual dates, not text that looks like dates. Use the DATEVALUE function or change the cell format to Date.
  • Criteria with operators: When using comparison operators, enclose them in quotes and concatenate with the value. For example, to find the minimum date after Jan 1, 2024: =MINIFS(A2:A20, A2:A20, ">"&DATE(2024,1,1)).
  • Wildcards: For text criteria, you can use * and ?. For instance, "A*" matches any cell starting with “A”.
  • Array formula not calculating: If you forget Ctrl+Shift+Enter, the formula may return 0. Re-enter it with the correct keystroke.
  • MINIFS not available: If you see #NAME?, your Excel version does not support MINIFS. Use the array formula or upgrade.
  • Dates as serial numbers: Excel stores dates as numbers; the minimum date is simply the smallest serial number. Format the result as a date to display it properly.

Frequently Asked Questions

Can I use MINIFS with a date range as a criterion?

Yes. To find the minimum date that is after a certain date, you can use =MINIFS(A2:A20, A2:A20, ">"&DATE(2024,1,1)). To use a cell reference for the date, say D1 contains the cutoff, use ">"&D1.

How do I find the minimum date ignoring blanks?

Add a criterion for the min_range itself: =MINIFS(A2:A20, A2:A20, "<>"). The "<>" means “not equal to blank”. This excludes empty cells from consideration.

What if my criteria are in a different sheet?

You can reference ranges on another sheet normally, e.g., =MINIFS(Sheet2!A:A, Sheet2!B:B, "Completed"). Just include the sheet name and an exclamation mark.

Does MINIFS work with dates stored as text?

No. MINIFS expects numeric dates. If your dates are text, convert them using DATEVALUE in a helper column, or use the array formula with the DATEVALUE function applied to each cell.

Can I use MINIFS with a condition based on today’s date?

Yes. For example, to find the earliest date that is today or later, use =MINIFS(A2:A20, A2:A20, ">="&TODAY()). This is useful for upcoming deadlines.

Is there a way to get the minimum date without a helper column?

Yes, MINIFS does it directly. If you need to filter by a calculated condition (e.g., dates within the last 30 days), you might need a helper column or an array formula, but MINIFS covers most simple criteria.

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.