Excel Find the Minimum Date With Criteria

How-to basics · Updated November 19, 2026

To find the earliest date that meets a condition in Excel, use MINIFS. If dates are in B2:B10 and the matching category is in A2:A10, with the category to find in D2, enter =MINIFS(B2:B10,A2:A10,D2) and format the result as a date.

How the MINIFS formula works

MINIFS returns the smallest number in a range after applying one or more conditions. Excel stores genuine dates as serial numbers, so the smallest date number is the earliest date. The function is available in Microsoft 365 and Excel 2019 or later; it is also available in Excel for the web. On Windows, type the formula in a worksheet cell and press Enter. On Mac, the formula works the same way, though ribbon and keyboard details can differ.

The formula has a minimum range first, followed by pairs of criteria ranges and criteria. In the opening example, B2:B10 contains the dates to compare, A2:A10 contains the values to check, and D2 holds the requested category. If D2 contains East, only rows whose category is East are considered. The result is the earliest date among those rows.

For example, suppose the data is:

  • East — March 12, 2025
  • West — March 2, 2025
  • East — January 28, 2025
  • East — February 14, 2025
  • West — January 5, 2025

With East as the criterion, the answer is January 28, 2025. The date in the West row is not considered, even though it might be earlier than some East dates.

Set up the ranges correctly

Each criteria range must cover the same rows as the minimum range. If the dates are in B2:B100, use a category range such as A2:A100—not A2:A99 or A:A paired with only part of the date column. Mismatched sizes can produce a #VALUE! error. Keeping the ranges aligned also makes the formula easier to audit.

For a simple category match, place the category to search for in a cell such as D2 and refer to that cell in the formula. This makes the result update when you change the selection, without editing the formula. You can also type a text criterion directly. With dates in B2:B10 and categories in A2:A10, this finds the earliest date for East: =MINIFS(B2:B10,A2:A10,"East").

Text criteria are generally not case-sensitive. A criterion of East will match east as well. Avoid extra spaces in category values; a trailing space can make a cell differ from the value you expect. If your source data may contain such inconsistencies, clean or standardize the category column before relying on the result.

Use more than one condition

For multiple conditions, add another criteria-range and criteria pair. Conditions in one MINIFS formula are combined as AND: a row must satisfy every condition. For instance, suppose A2:A10 contains regions, B2:B10 contains dates, and C2:C10 contains status values. This returns the earliest date for East rows whose status is Closed:

With regions in A2:A10, dates in B2:B10, statuses in C2:C10, and the required region in D2, use:

=MINIFS(B2:B10,A2:A10,D2,C2:C10,"Closed")

You can also apply numeric conditions, such as a quantity greater than zero, or date conditions, such as dates on or after a cutoff. For operators like greater than or equal to, join the operator to a cell reference with an ampersand. With dates in B2:B10, regions in A2:A10, East in D2, and a start date in E2, this finds the earliest East date on or after the start date:

=MINIFS(B2:B10,A2:A10,D2,B2:B10,">="&E2)

Here the date column appears both as the minimum range and as a criteria range. That is valid: Excel first filters the dates using the cutoff, then finds the smallest qualifying value. To impose an end date as well, add a second condition on B2:B10 using a less-than-or-equal-to operator.

Find a date within a date window

A common variation is finding the earliest date in a period, such as a month or reporting window. If the start and end dates are stored in E2 and F2, use two criteria on the date column. With dates in B2:B10 and regions in A2:A10, this finds the earliest East date from E2 through F2, including both endpoints:

=MINIFS(B2:B10,A2:A10,"East",B2:B10,">="&E2,B2:B10,"<="&F2)

In the formula, the HTML text &lt;= represents the less-than-or-equal operator; when entering the formula in Excel, type the ordinary characters <= inside the quoted criterion. Set E2 and F2 to real Excel dates, not text that merely looks like a date. A quick way to check is to change the cell’s number format to General: an actual date displays as a number.

Account for timestamps and time values

A date-and-time value includes a fractional part representing the time of day. That matters when you want all records on a calendar date. A criterion equal to a date at midnight will not match a timestamp later that same day. Instead, use a lower bound at the start of the day and an exclusive upper bound at the start of the next day.

With timestamps in B2:B10 and the target calendar date in E2, this finds the earliest timestamp occurring on that day:

=MINIFS(B2:B10,B2:B10,">="&E2,B2:B10,"<"&E2+1)

When entering it in Excel, type the normal < operator. The upper bound excludes midnight at the beginning of the following day, while including every time on the target date. Format the result to show a date and time if you need to see the time portion. A date-only format can hide that portion without removing it.

Handle no matches, blanks, and invalid dates

MINIFS returns 0 when no cells meet the criteria. Since zero may display as a date in some date systems, that can look like a surprising old date rather than a missing result. Add a matching count check when you want a clear message instead. With dates in B2:B10, categories in A2:A10, and the requested category in D2, use:

=IF(COUNTIFS(A2:A10,D2)=0,"No match",MINIFS(B2:B10,A2:A10,D2))

This displays No match if the category does not occur. If the category exists but its date cells are blank or stored as text, the result may still be zero because there is no usable numeric date to minimize. Check that the matching rows contain real Excel dates, and consider testing for numeric values if blanks are common. Format the formula cell as a date only after confirming it returns a valid date serial.

Blank cells in the minimum range are not valid dates and do not provide an earlier date. Text that looks like a date is also not necessarily a date value: imported data may contain text such as 2025-01-28. Convert those entries to real dates before using them. Depending on how the text is structured and your regional settings, Excel may interpret a conversion differently, so verify a sample rather than assuming every imported date was parsed correctly.

Handle an OR condition

Because conditions inside MINIFS use AND logic, asking for East or Central requires a different approach. In Microsoft 365 or Excel 2021 and later, FILTER can select rows that match either category, and MIN can return the earliest selected date. With categories in A2:A10 and dates in B2:B10, use:

=IFERROR(MIN(FILTER(B2:B10,(A2:A10="East")+(A2:A10="Central"))),"No match")

The plus sign between the tests acts as OR for this filter: a row is included if either test is true. IFERROR supplies a message when there are no matching rows. This approach requires a version with FILTER; it is not available in older perpetual Excel versions such as Excel 2019. If you are using an older version, you can calculate each category’s minimum separately and compare the results, while taking care to exclude categories with no match so a returned zero is not mistaken for an early date.

Choose the right method and troubleshoot results

MINIFS is usually the clearest option for one or more AND conditions. Use FILTER with MIN when you need a flexible OR test and have a supported version. Older Excel versions without MINIFS can use an array formula, but it is less straightforward to maintain. With categories in A2:A10 and dates in B2:B10, the older array expression for East is:

=MIN(IF(A2:A10="East",B2:B10))

In older Excel versions, confirm this as an array formula with Ctrl+Shift+Enter rather than Enter alone; Excel displays braces around it when entered that way. In Microsoft 365 and Excel 2021 or later, dynamic array calculation allows you to enter it with Enter. This alternative can return zero when there are no matches, so add a separate check if absence of matches is possible.

If the result looks wrong, check these points:

  • Confirm that the date cells are numeric Excel dates rather than text.
  • Verify that all ranges start and end on the same rows.
  • Check the criterion for hidden spaces, spelling differences, or an incorrect cell reference.
  • For timestamp data, use a range of times rather than equality to a date at midnight.
  • Confirm the result cell’s number format; a correct serial number can look like an ordinary number or an unexpected date when formatted differently.

In a structured Excel table, you can use table column references instead of fixed cell ranges. This can make a formula expand as new rows are added, but the exact column names depend on your table. The underlying rule stays the same: the date column is the minimum range, and each condition points to the column containing the value to test.

Frequently Asked Questions

Can MINIFS return the earliest date for several criteria?
Yes. Add a criteria-range and criterion pair for each condition. The conditions are AND conditions, so a qualifying row must satisfy them all.

Why does MINIFS show January 1900 or zero?
It may have found no matching numeric dates and returned 0, or the result may be formatted as a date. Check whether a match exists and whether the source values are genuine dates. A COUNTIFS check can display a message instead of zero.

Can I find the earliest date after a date in another cell?
Yes. Add the date column as a criteria range and use a criterion such as greater than or equal to the cutoff cell. Join the operator and cell reference with &.

Does MINIFS work with date and time values?
Yes, because Excel stores date-and-time values as numbers. For all times on a particular calendar day, use a lower bound at that date and an exclusive upper bound at the next day.

What if I need the earliest date for either of two categories?
That is an OR condition, which a single set of MINIFS criteria does not express. In Microsoft 365 or Excel 2021 and later, use FILTER to select both categories and MIN to find the earliest date; handle the no-match case if needed.

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.