Excel formula: First in, last out times

Generic formula

=MINIFS(times,names,name,actions,action)

Explanation

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

How this formula works

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

To get the maximum value from a set of numbers, use the MAX function. In the example shown, the formula in cell I6 is: = MAX ( C6:G6 ) The MAX function is fully automatic. It accepts one or more arguments, which can be numbers or cell references...

Minimum value

To get the minimum value from a set of numbers, use the MIN function. In the example shown, the formula in cell I6 is: = MIN ( C6:G6 ) The MIN function is fully automatic. It accepts one or more arguments, which can be numbers or cell references...

Maximum if multiple criteria

To get the maximum value in a data set based on more than one criteria (i.e. to get MAX IF), you can use an array formula based on the MAX and IF functions. In the example shown, the formula in I6 is: { = MAX ( IF ( color = G6 , IF ( item = H6 ,...

Minimum if multiple criteria

To get the minimum value in a data set using multiple criteria (i.e. to get MIN IF), you can use and array formula based on the MIN and IF functions. In the example shown the formula in I6 is: { = MIN ( IF ( color = G6 , IF ( item = H6 , price...

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 a range of values. The MAX function ignores empty cells, the logical values TRUE and FALSE, and text values.

Excel IF Function

The IF function can perform a logical test and return 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.