Max Min Differnce in a Range
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?
"Shane Devenshire" wrote:
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.
--
If this helps, please click the Yes button.
Cheers,
Shane Devenshire
"Albert H. Bell" wrote:
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.
|