Thread: Min and Max for 2nd shift times

Hi, Trying to get the min and max times for a list of times. This seems to work great on 1st shift, however on second shift they are all messed up.

I included a sample workbook to show my problem.

Can someone advise how to get the min and max from the times listed in col A. I guess it has to do with times before and after midnight

1st shift works 5am to 3:30 somtimes 5 pm m-f
2nd shift works 3:30 to 2am sometime 4 am m-f


Thanks as always, Mike

Hi Mike,

Try these array formulas for second shift (confirmed with CTRL+SHIFT+ENTER):

=MIN(IF(A2:A270>=TIME(15,30,0),A2:A270))

=MAX(IF(A2:A270<TIME(5,0,0),A2:A270))

This will get the minimum time value starting at 15:30 (3:30pm), and the maximum time value earlier than 5:00am respectively. Format the cells as Time.

Hi Mike

Hoping that i get your point,i believe that is midnight problem and excel does not "knows" what date is it. i suggest this:

Greate one more helper(hide, if you like)and put the dates there.

Then use MAX for dates.

Then use this for getting the max hOUR...

=MAX(( A2:A170= A1)*B2:B170)

Array formula. CSE,Not just enter.

Hope to helps you.