This works becauseExcel timesandExcel datesarenumericvalues.
Excel Table
For convenience, all data is in anExcel Tablenameddatain the range B5:D16.
If you are new to Excel Tables,this article provides an overview.
The MINIFS function returns the smallest numeric value that meets one or more supplied criteria.
The actions in the data are the text values “In” and “Out”.
We start off with themin_range, which is the range that contains the numeric time values.
Notice that H$4 is amixed referencewith the row locked.
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.
Like the MINIFS function, the MAXIFS function retrieves a value that meets one or more supplied criteria.
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.
These functions are built for convenience, but they have some quirks,explained in more detail here.
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.
The MIN function ignores empty cells, the logical values TRUE and FALSE, and text values.
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.
More than one condition can be tested by nesting IF functions.
The output from FILTER is dynamic.
If source data or criteria change, FILTER will return a new set of results.
