How to find minimum first and last hour for each shift in Excel

Question

I want to find first and last time for each shift of the day:

Shift	Start Hour	End Hour
Day Shift	7:00	15:00
Day Shift	15:00	22:00
Night Shift	23:00	02:00
Night Shift	02:00	06:00

Shift	First Hour	Last Hour
Day Shift	7:00	22:00
Night Shift	23:00	06:00

I have tried MIN and MAX array formula, it works on day shift because the last hour is always bigger (22:00), but at night shift, formula calculates 22:00 as biggest hour, not 6:00. We are using 24 hour format.

I am using 2019 Microsoft Excel Thank you in advance.

Answer 1

In Excel 2019 you should be able to use Minifs and Maxifs for the day shift:

=MINIFS(B2:B5,A2:A5,F2)

and

=MAXIFS(C2:C5,A2:A5,F2)

but as you say the night shift would need adjustment and you couldn't use Maxifs or Minifs because they don't work with arrays e.g.

=MOD(MIN(IF(A2:A5=F3,B2:B5+(B2:B5<0.5))),1)

and

=MOD(MAX(IF(A2:A5=F3,C2:C5+(C2:C5<0.5))),1)

0.5 is equivalent to half a day (12 hours) so I am assuming that if the night shift starts or ends before midday, it is into the next day so I will add 1 (a whole day or 24 hours) before finding the min or max, then take the result mod 1 to reduce it to a part of a day (i.e. a number of hours).

---

If there are blanks in the day shift, Maxifs will take care of them as it will ignore them.

If there are blanks in the night shift, try:

=MOD(MIN(IF(A2:A5=F3,IF(B2:B5<>"",B2:B5+(B2:B5<0.5)))),1)

and

=MOD(MAX(IF(A2:A5=F3,IF(C2:C5<>"",C2:C5+(C2:C5<0.5)))),1)