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

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...

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...

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...

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

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

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

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

Excel IF Function

The Excel 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....

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

Exceljet

Quick, clean, and to the point

First in, last out times

Array formula alternatives

Excel Formula Training

Download 100+ Important Excel Functions

Excel video training