Get earliest and latest project dates

This example shows how to retrieve the earliest and latest dates associated with a project. In the example shown, the formulas in H5 and I5 are:

=MINIFS(data[Start],data[Project],G5) // earliest
=MAXIFS(data[End],data[Project],G5) // latest

where "data" is an Excel table as shown, and project names in column G match those in column B.

Note: MINIFS and MAXIFS are available only in Excel 365 and Excel 2019. In other versions of Excel, you can use a simple array formula, as explained below.

Introduction

The task here is to find the earliest and latest dates associated with a given project. The earliest dates come from the Start column, and latest dates come from the End column.

You might be tempted to use a lookup function like VLOOKUP, XLOOKUP, or INDEX and MATCH. However, since each project has more than one entry, and entries may not always be sorted by date, this becomes challenging.

A better approach is to use process of elimination: discard dates for other projects, and work only with the dates that are left.

Explanation

The MINIFS function returns the smallest numeric value that meets supplied criteria, and the MAXIFS function returns the largest numeric value that meets supplied criteria.

Like COUNTIFS and SUMIFS, these functions use range/criteria "pairs" to apply conditions. For both formulas, we need just one condition: the project name must equal the name in column G:

data[Project],G5 // condition

To get the earliest start date, we use:

=MINIFS(data[Start],data[Project],G5) // earliest date

Here, MINIFS returns the minimum value in the Start column where the project is equal to "Omega" (from cell G5). Since Excel dates are just numbers, the minimum date is the same as the earliest date.

To get the latest end date, we use:

=MAXIFS(data[End],data[Project],G5) // latest date

Here, MAXIFS returns the maximum value in the End column where the project is equal to "Omega". As above, the maximum value is the same as the latest date.

Array formula alternative

If you don't have MINIFS and MAXIFS, you can use simple array formulas, based on the MIN and MAX functions, to get the same result. For the earliest start date:

{=MIN(IF(data[Project]=G5,data[Start]))}

For the latest end date:

{=MAX(IF(data[Project]=G5,data[End]))}

Note: both formulas are array formulas and must be entered with control + shift + enter, in Excel 2019 or earlier. With Excel 365, you can enter the formulas normally, since array formulas are native.

In both cases, the IF function is used to "filter" date values like this:

IF(data[Project]=G5,data[End]) // filter dates by project

When G5 is "Omega", IF returns the end date. Otherwise, IF returns FALSE. Since we are testing all project names in the table at the same time, the result is an array of values like this:

{43936;43983;43990;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

The large serial numbers are Excel dates associated with project Omega. The other values are FALSE, since the project is not Omega. Because MIN and MAX are programmed to ignore the logical values TRUE and FALSE, they only operate on the remaining values. MIN returns the smallest (earliest) date, and MAX returns the largest (latest) date.