Maximum and Minimum Time Value in a Range

I have used max and min function to display minimum and maximum time value; however, it is not displaying the correct time value from the list. I think there is a better formula to achieve this. From the sample data displayed below, minimum time value should be 11:30PM and maximum time value should be 6:30AM

See sample data below:

4/1/2013 11:45 PM
4/1/2013 11:30 PM
4/2/2013 6:30 AM
4/2/2013 6:15 AM
4/2/2013 6:00 AM
4/2/2013 5:45 AM
4/2/2013 5:30 AM
4/2/2013 1:30 AM
4/2/2013 1:15 AM
4/2/2013 1:00 AM
4/2/2013 12:45 AM
4/2/2013 12:30 AM
4/2/2013 12:15 AM
4/2/2013 12:00 AM

Mmmm. Don't you need a date field too??? Otherwise, how is Excel meant to know that it is either a) earlier that day or b) early the following day? Maybe I'm being htick, as Excels way of handling dates and times has always felt like a bit of a dark art...

Try this...

Data Range

	A	B	C	D
1	------	------	------	------
2	4/1/2013 11:45 PM		Min	11:30 PM
3	4/1/2013 11:30 PM		Max	6:30 AM
4	4/2/2013 6:30 AM
5	4/2/2013 6:15 AM
6	4/2/2013 6:00 AM
7	4/2/2013 5:45 AM
8	4/2/2013 5:30 AM
9	4/2/2013 1:30 AM
10	4/2/2013 1:15 AM
11	4/2/2013 1:00 AM
12	4/2/2013 12:45 AM
13	4/2/2013 12:30 AM
14	4/2/2013 12:15 AM
15	4/2/2013 12:00 AM
16

D2: =MOD(MIN(A2:A15),1)
D3: =MOD(MAX(A2:A15),1)

Format as Time

If the dates are all the same then what results do you expect?

Originally Posted by bjnockle

Same result for maximum and minimum time. Thanks

That's what I was afraid of!

Explain the logic of how 11:30:00 PM is the min value and 6:30:00 AM is the max value.

When the dates are all the same it seems that the expected results are the opposite of the actual true values.

OK, The OP's edit added the date field, as I suggested, but I am also stumped as to how 06:30 can be the maximum time, when the same data range (all on the same day) also contains 23:45.

bjnockle - can you explain????