First in last out times

In this problem, the goal is to find the first (earliest) time in and the last (latest) time out for a given name. This is essentially a lookup problem and the solution shown in the worksheet is an example of how you can sometimes use minimum and maximum functions to perform lookups. This works because Excel times and Excel dates are numeric values.

Excel Table

For convenience, all data is in an Excel Table named data in the range B5:D16. Excel Tables are a convenient way to work with data in Excel because they (1) automatically expand to include new data and (2) offer structured references, which let you refer to data by name instead of by address. If you are new to Excel Tables, this article provides an overview. Also see this short video:

MINIFS function

To get the first (earliest) time in time for a given name, you can use the MINIFS function. The MINIFS function returns the smallest numeric value that meets one or more supplied criteria. Each condition is entered with a range and criteria, and the generic syntax for MINIFS with two criteria looks like this:

=MINIFS(min_range,range1,criteria1,range2,criteria2)

In this case, we will need to apply two conditions: (1) check for the correct name and (2) check for the correct "action". The actions in the data are the text values "In" and "Out". We start off with the min_range, which is the range that contains the numeric time values. We supply the Time column using the structured reference data[Time]:

=MINIFS(data[Time],

Next, we provide the first condition, which checks if the name in the data equals the name in cell G5:

=MINIFS(data[Time],data[Name],G5,

The criteria_range is given as data[Name] and criteria is supplied as cell G5. The second condition tests for the "In" action:

=MINIFS(data[Time],data[Name],G5,data[Action],H$4)

In this case, range is provided as data[Action] and criteria is provided as H4. Notice that H$4 is a mixed reference with the row locked. We do this so we can copy the formula down in the next row and while keeping the action locked to cell H4.

When the formula is entered, the result is 6:55 AM. This is the earliest "In" time for Juan. When the formula is copied down to the next row, the result is 7:45 AM. This is the earliest "In" time for Sarah.

MAXIFS function

To get the last (latest) time out for a given name, you can use the MAXIFS function. Like the MINIFS function, the MAXIFS function retrieves a value that meets one or more supplied criteria. The generic syntax for MAXIFS with two criteria looks like this:

=MAXIFS(max_range,range1,criteria1,range2,criteria2)

To retrieve the last time out for a given name, we need to apply two conditions: (1) check for the correct name and (2) check for the "out" action.  We start off with the max_range, which contains times:

=MAXIFS(data[Time],

Then we provide a condition to check the name in the data against the name in cell G5:

=MAXIFS(data[Time],data[Name],G5,

The criteria_range is given as data[Name] and criteria is supplied as cell G5. Then we add the second condition to test for the "Out" action:

=MAXIFS(data[Time],data[Name],G5,data[Action],I$4)

As above with the MINIFS function, we use a mixed reference for I$4 to look at the row. When the formula is entered, the result is 5:40 PM. This is the last "Out" time for Juan. When the formula is copied down to the next row, the result is 6:45 PM. This is the last "Out" time for Sarah.

FILTER function

The MINIFS and MAXIFS functions are in a group of eight functions I call "RACON functions". These functions are built for convenience, but they have some quirks, explained in more detail here. The biggest limitation is that you can't use RACON functions on arrays of data, you must provide an actual range. If you need to calculate minimum and maximum values based on one or more criteria with data in an array, you can use the FILTER function with the MIN and MAX functions like this:

=MIN(FILTER(data[Time],(data[Name]=G5)*(data[Action]=H$4)))
=MAX(FILTER(data[Time],(data[Name]=G5)*(data[Action]=I$4)))

These are drop-in replacements for the MINIFS and MAXIFS formulas described above. The gist is that the FILTER function applies criteria and hands off the filtered times to MIN and MAX, which return a final result. I personally prefer this option because it uses standard syntax and will work on ranges as well as arrays. However, FILTER is a newer function in Excel and not available in older versions.

Array formula alternatives

The MAXIFS and MINIFS functions were introduced in Excel 2016. If you are using an older version of Excel, you can use an array formula instead:

{=MIN(IF(data[Name]=G5,IF(data[Action]=H$4,data[Time])))}
{=MAX(IF(data[Name]=G5,IF(data[Action]=I$4,data[Time])))}

Note: these are array formulas and must be entered with control + shift + enter in Legacy Excel.