Conditional dates in Excel: MINIFS and MAXIFS

You need the first order date for a city or the most recent event within a particular year. I solve that by selecting the smallest or largest date among records meeting the conditions. Excel provides MINIFS and MAXIFS for this task.

The former Polish page used DATA.WARUNKI, a function name that does not exist in Excel, along with invalid names for conditional minimum and maximum. The corrected topic is finding date extremes with criteria. The functions below are available in Microsoft 365 and Excel 2019, 2021 and 2024.

Start with numeric dates and aligned ranges

Excel stores valid dates as serial numbers. Within the same date system, a smaller serial number represents an earlier date. Text that resembles a date needs appropriate conversion first; applying a date display format does not guarantee the correct underlying type.

The MINIFS documentation puts the range to minimise first, followed by a criteria range and its criterion:

=MINIFS(date_range,criteria_range,criterion)
=MAXIFS(date_range,criteria_range,criterion)

Add further conditions as range-and-criterion pairs. All conditions must hold for the same record. Ranges need the same size and shape. They also need to refer to corresponding rows: shifting a range by one cell can produce a technically valid but analytically incorrect result.

English examples use commas between arguments. The Polish version uses localised names and semicolons. Your environment’s separators depend on regional settings.

A small order table

Assume headers occupy A1:C1 and five synthetic orders occupy rows 2:6:

The worksheet-row column is explanatory; do not paste it as an extra column before A. Enter =DATE(2024,1,10) in C2, =DATE(2024,1,12) in C3, =DATE(2024,1,5) in C4, =DATE(2024,2,1) in C5 and =DATE(2025,1,2) in C6. Apply a date format.

That avoids guessing whether pasted text such as 2024-01-10 will be recognised by your installation. These examples assume complete, known dates. Assessing an incomplete export is a separate step.

Find the earliest date for a city

For Warsaw:

=MINIFS(C2:C6,B2:B6,"Warsaw")

First select rows 2, 4 and 6 because their city meets the criterion. Their dates are 10 January 2024, 5 January 2024 and 2 January 2025. The earliest is 2024-01-05, which is the displayed result with an appropriate date format.

Worksheet order does not select the answer. The function finds the minimum matching value rather than returning the first matching row. The 10 January record appears higher in the sheet, but the minimum still comes from 5 January.

If you need the order ID associated with that date, the minimum function alone does not return it. Two orders on the same earliest day would also require a tie-breaking rule or a list of all matching orders.

Find the latest date for a city within a year

To find the last Warsaw order in 2024:

=MAXIFS(C2:C6,B2:B6,"Warsaw",C2:C6,">="&DATE(2024,1,1),C2:C6,"<"&DATE(2025,1,1))

The city criterion initially retains three records. The year boundaries exclude 2 January 2025, leaving 5 and 10 January 2024. The function selects the larger date: 2024-01-10.

Here, MAXIFS receives two additional conditions on the date range. ">="&DATE(...) joins the comparison operator to a numeric boundary date. There is no need to compare month names or add a separate year column.

The upper boundary is excluded. That pattern is also useful for data containing times, but this exercise uses dates without times. With timestamps, establish the time zone and meaning of the recorded event as well.

No match can look like a date

There are no Poznan orders in the synthetic table. MINIFS and MAXIFS can return zero when no records meet the criteria, as Microsoft’s no-match examples show. A date format can then display something near the start of the date system instead of a clear “no data” message.

For a report, I therefore add an explicit count check. Put the requested city in E2. This formula assumes modern, positive numeric dates in C2:C6 and excludes non-positive values:

=IF(COUNTIFS(B2:B6,E2,C2:C6,">0")=0,"No dates",MINIFS(C2:C6,B2:B6,E2,C2:C6,">0"))

COUNTIFS checks the same conditions as the minimum. Warsaw has a count of 3; Poznan has a count of 0. The formula returns a date or the text No dates. This deliberately mixes result types. It can work for presentation, while further calculations may benefit from keeping the date and availability status in separate cells.

If you add year boundaries to the minimum calculation, add them to the count as well. Counting all years while selecting a date from only one year does not reliably detect missing matches.

What I check when a result looks wrong

I first inspect a few date values using the General format. Actual dates show serial numbers. If the source mixes text and numeric dates, I normalise it before selecting the minimum. I avoid wrapping the whole calculation in error handling that hides a broken import.

Then I check range alignment, city spelling and extra spaces. B2:B6 and C3:C7 have the same dimensions but represent different row pairs. Excel cannot infer the intended business alignment from the headings and repair that shift.

This entry concerns extreme dates. Moving a date by a number of months is a separate task for EDATE, while building a date from year, month and day is covered in the DATE entry. Sharing a calendar theme does not make these operations interchangeable.

Exercise: reorder the records and change the city

For Warsaw, predict the minimum and maximum without a year restriction. The expected dates are 2024-01-05 and 2025-01-02. Now reorder all five complete records. The results should remain unchanged because the functions select date values rather than worksheet positions.

Finally, put a city absent from the table into E2. I want an explicit indication that no dates are available rather than an accidentally formatted zero. It is a small formula change with a substantial effect on how the report is understood.