Exceljet

Quick, clean, and to the point

First in, last out times

Excel formula: First in, last out times
Generic formula 
=MINIFS(times,names,name,actions,action)
Summary 

To get "first in, last out" times from timesheet data, you can use the MAXIFS and MINIFS functions, or an array formula as described below. In the example shown, the formula in H6 is:

=MINIFS(times,names,H5,actions,"in")

with the following named ranges: actions = C5:C22. names = B5:B22, times = E5:E22

Explanation 

The MINIFS function is designed to return minimum values in a range based on one or more criteria. In this case, the range we are extracting a minimum value from is E5:E22), named "times".

We are filtering these values first using two range/criteria pairs. The first pair provides a range of B5:B22 (names), with the name in H5 for criteria. The second pair uses the range C5:C22 (actions), with "in" as a criteria.

MINIFS then returns the earliest (minimum) time where name is "Max" and action is "in".

The "last out" time is calculated in a similar way with the MAXIFS function:

=MAXIFS(times,names,H5,actions,"out")

Array formula alternatives

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

{=MIN(IF(names=K5,IF(actions="in",times)))}
{=MAX(IF(names=K5,IF(actions="out",times)))}

Note: these are array formulas and must be entered with control + shift + enter. Excel will add the curly braces automatically.

Author 
Dave Bruns
Related formulas 
Excel formula: Maximum value
Maximum value
The MAX function accepts one or more arguments , which can be a mix of constants, cell references, and ranges. MAX then returns the maximum value in the data provided. Text values and empty cells are ignored. In this example, each student has five...
Excel formula: Minimum value
Minimum value
The MIN function accepts one or more arguments , which can be a mix of constants, cell references, and ranges. The MIN function returns the maximum value in data provided. Text values and empty cells are ignored. In this example, each student has...
Excel formula: Maximum if multiple criteria
Maximum if multiple criteria
This example uses the following named ranges : "color" = B6:B14, "item" = C6:C14, and "price" = E6:E14. The goal is to find the maximum price for a given color and item. This formula uses two nested IF functions, wrapped inside MAX to return the...
Excel formula: Minimum if multiple criteria
Minimum if multiple criteria
This example uses the following named ranges : "color" = B6:B14, "item" = C6:C14, and "price" = E6:E14. In the example, we have pricing on items in various regions. The goal is to find the minimum price for a given color and item. This formula uses...
Related functions 
Excel MINIFS function
Excel MINIFS Function

The Excel MINIFS function returns the smallest numeric value that meets one or more criteria in a range of values. MINIFS can be used with criteria based on dates, numbers, text, and other conditions. MINIFS supports logical operators (>,...

Excel MAXIFS function
Excel MAXIFS Function

The Excel MAXIFS function returns the largest numeric value that meets one or more criteria in a range of values. MAXIFS can be used with criteria based on dates, numbers, text, and other conditions. MAXIFS supports logical operators (>,...

Excel MAX function
Excel MAX Function

The Excel MAX function returns the largest numeric value in a range of values. The MAX function ignores empty cells, the logical values TRUE and FALSE, and text values.

Excel IF function
Excel IF Function

The IF function runs a logical test and returns one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions. The IF...

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

500 Formulas | 101 Functions

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.