If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Max Min Differnce in a Range
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. |
#2
|
|||
|
|||
Max Min Differnce in a Range
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. |
#3
|
|||
|
|||
Max Min Differnce in a Range
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)) ? |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
Max Min Differnce in a Range
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)) ? |
#6
|
|||
|
|||
Max Min Differnce in a Range
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. |
#7
|
|||
|
|||
Max Min Differnce in a Range
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. |
Thread Tools | |
Display Modes | |
|
|