Max Min Differnce in a Range

A

Albert H. Bell

I have two formulas that I want to create.

I have data from an assembly equipment tester that has the following columns
A B C D
Date Time Date and Time Difference
6/1/2009 7:00:42 6/1/09 7:00
6/1/2009 7:01:30 6/1/09 7:01 0:00:48
6/1/2009 16:47:52 6/1/09 16:47 0:01:15
6/1/2009 16:48:50 6/1/09 16:48 0:00:58
6/1/2009 16:49:39 6/1/09 16:49 0:00:49
6/2/2009 7:02:01 6/2/09 7:02 14:12:22
6/2/2009 7:03:07 6/2/09 7:03 0:01:06
6/2/2009 7:04:03 6/2/09 7:04 0:00:56
6/2/2009 7:04:54 6/2/09 7:04 0:00:51

The data will continue for an entire month and for about 500 lines a day. I
want to extract the start time and stop time for each day of production to
determine the total production time. (For this Data, it would be 6/1/09 16:48
minus 6/1/09 07:00. I am thinking I need to use a Max, Min & Index function
but I don't know how to combine the just right.

In addition, I want to sum the differences only if they are over a given
time as defined by another cell (in my case it is in B4), but I do not want
to include the time between the end of one day and the start of the following
day.

S

Shane Devenshire

Hi,

Suppose your start times are in column A and the end time in column B then
in a blank column enter the earliest day you want, I entered 6/1/2009 in E1.
Then enter the following two formulas in F1 and G1. Copy the date and
formulas down as far as you need.

=MIN(IF(DATE(YEAR(A\$2:A\$10),MONTH(A\$2:A\$10),DAY(A\$2:A\$10))=E1,A\$2:A\$10,""))

=MAX(IF(DATE(YEAR(B\$2:B\$10),MONTH(B\$2:B\$10),DAY(B\$2:B\$10))=E1,B\$2:B\$10,""))

Both of these formulas are arrays - that means you enter them by pressing
Shift+Ctrl+Enter before you copy then down.

There may be a number of ways to simplify these depending on other info.

H

Harlan Grove

Shane Devenshire said:
=MIN(IF(DATE(YEAR(A\$2:A\$10),MONTH(A\$2:A\$10),DAY(A\$2:A\$10))=E1,A\$2:A\$10,""))

=MAX(IF(DATE(YEAR(B\$2:B\$10),MONTH(B\$2:B\$10),DAY(B\$2:B\$10))=E1,B\$2:B\$10,""))
....

Why not just

=MIN(IF(INT(A\$2:A\$10)=E1,A\$2:A\$10))

and

=MAX(IF(INT(B\$2:B\$10)=E1,B\$2:B\$10))

?

A

Albert H. Bell

Shane,

I think the concept is close. I think there are a few details I might not
have explained clearly.
1) There is not a true start time and stop time. I only have the date and
Times in column C. In column C, for a given day there is the earliest entry
and the last entry. From those two values I want to calculate the "run time"
or difference between the start and stop.
2) When I include a range that goes across more than one day (for example
6/2/2009) and the Min Rage also goes into a multiple Day I return #Value.

3) Any thoughts are the second part of the question?

A

Albert H. Bell

Harlan,

I have the same problem with your formula. If the Int Formula contains more
than 1 date I return #Value.

H

Harlan Grove

Albert H. Bell said:
I have the same problem with your formula.  If the Int Formula contains more
than 1 date I return #Value.

"Harlan Grove" wrote: ....

Then you didn't enter the formula as an array formula. To do so, you
need to hold down a [Ctrl] key and a [Shift] key before pressing the
[Enter] key.

A

Albert H. Bell

Jackpot! Thanks

Harlan Grove said:
Albert H. Bell said:
I have the same problem with your formula. If the Int Formula contains more
than 1 date I return #Value.

"Harlan Grove" wrote: ....

Then you didn't enter the formula as an array formula. To do so, you
need to hold down a [Ctrl] key and a [Shift] key before pressing the
[Enter] key.