Search code examples
excelexcel-formulaexcel-2013

First Time In and Last Time Out with Excel

I have a data below where I want to get the unique ID number and only get the first time in and last time out. The data looks something like this:

enter image description here

The result should be something like this:

enter image description here

Solution

  • Since your times are text that look like times we need to convert them.

    We can do this in formula using two nested SUBSTITUTES.

    To get the MIN and MAX we will use the AGGREGATE formula.

    MIN:

    =AGGREGATE(15,6,SUBSTITUTE(SUBSTITUTE($D$2:$D$7,"AM"," AM"),"PM"," PM")/($A$2:$A$7=A12),1)

    MAX:

    =AGGREGATE(14,6,SUBSTITUTE(SUBSTITUTE($F$2:$F$7,"AM"," AM"),"PM"," PM")/($A$2:$A$7=A12),1)

    enter image description here

    You can do the same with array formulas MIN(IF()) and MAX(IF()).

    =MIN(IF($A$2:$A$7=A12,--SUBSTITUTE(SUBSTITUTE($D$2:$D$7,"AM"," AM"),"PM"," PM")))
=MAX(IF($A$2:$A$7=A12,--SUBSTITUTE(SUBSTITUTE($F$2:$F$7,"AM"," AM"),"PM"," PM")))

    As they are array formulas they need to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly Excel will put {} around the formula.

    Both Sets of formula will return a decimal. The cell will need to be formatted to the proper desired output.