Using MIN and MAX Function to find times

Hi everyone:

I am trying to find the minimum and maximum start and end times from a column of dates and times. I've attached a sample Excel document.

Originally, I tried to use the MIN function for the minimum start time.
Instead of using 3/8/10 11:55 AM as the minimum start time (Cell B4),
I want to use 1:25 AM instead (Cell B2).

The same goes for the MAX function for the maximum start time.
Instead of using 3/18/2010 1:25:26 AM as the maximum start time (Cell B2),
I want to use 4:15 PM instead (Cell B3)

Using tips from previous replies to my post, I thought I could extract the times (Columns E and F) and use the MIN & MAX functions. Instead, Excel is showing 12:00:00 AM in all of the summary fields (F7, F8, F9, and F10).

Thanks in advance for your help (again)!

It appears that you want to find the minimum and maximum times regardless of which day they occur.

To find the min/max, you have to truncate the date/time to just the time. In Excel, date/time is stored as a real number of days, where the part after the decimal point is the fraction of a day. If you drop the part to the left of the decimal point you are left with just the time.

See attached for a solution, which does not need any helper columns. In the MIN/MAX fields I have used an array formula to calculate the time portion, then find the min or max.

Please let me know if you have questions.

JeanRage's attachment has a good explanation, which I'll repeat and expand here.

I am using an array formula (search Excel help for "array formula"). The array formula is a powerful feature in Excel that allows a limited type of iteration for all cells in a given range. In this case, we need to know the minimum time. The formula to look at a date/time in B2 and return time is:

=$B$2-INT($B$2)

We need to do the same thing for multiple cells and then find the min. To do this, an array formula will create an array of values that is the result of this calculation, then we apply the function to the entire array. To start along this path we create the formula:

=MIN($B$2:B$4-INT($B$2:$B$4))

For Excel to interpret this as an array formula, you must enter the formula by pressing CTRL-SHIFT-ENTER. (If the formula already exists, you must press F2 to edit then press CTRL-SHIFT-ENTER.) Then you will see

{=MIN($B$2:B$4-INT($B$2:$B$4))}

The braces are added by Excel when you enter an array formula; you cannot type them in yourself.