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.

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.

Shane Devenshire wrote...
....
=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))

?

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.

Harlan,

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

"Harlan Grove" wrote:

Shane Devenshire wrote...
....
=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))

?

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

"Harlan Grove" wrote:
....
=MIN(IF(INT(A$2:A$10)=E1,A$2:A$10))
....
=MAX(IF(INT(B$2:B$10)=E1,B$2:B$10))

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.

Jackpot! Thanks

"Harlan Grove" wrote:

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

"Harlan Grove" wrote:
....
=MIN(IF(INT(A$2:A$10)=E1,A$2:A$10))
....
=MAX(IF(INT(B$2:B$10)=E1,B$2:B$10))

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.