A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Max Min Differnce in a Range



 
 
Thread Tools Display Modes
  #1  
Old June 12th, 2009, 04:16 AM posted to microsoft.public.excel.worksheet.functions
Albert H. Bell[_2_]
external usenet poster
 
Posts: 6
Default 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  
Old June 12th, 2009, 04:51 AM posted to microsoft.public.excel.worksheet.functions
Shane Devenshire[_3_]
external usenet poster
 
Posts: 3,333
Default 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  
Old June 12th, 2009, 06:14 AM posted to microsoft.public.excel.worksheet.functions
Harlan Grove[_2_]
external usenet poster
 
Posts: 1,439
Default 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  
Old June 12th, 2009, 09:16 PM posted to microsoft.public.excel.worksheet.functions
Albert H. Bell[_2_]
external usenet poster
 
Posts: 6
Default 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  
Old June 12th, 2009, 09:26 PM posted to microsoft.public.excel.worksheet.functions
Albert H. Bell[_2_]
external usenet poster
 
Posts: 6
Default 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  
Old June 12th, 2009, 11:50 PM posted to microsoft.public.excel.worksheet.functions
Harlan Grove[_2_]
external usenet poster
 
Posts: 1,439
Default 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  
Old June 14th, 2009, 02:43 AM posted to microsoft.public.excel.worksheet.functions
Albert H. Bell[_2_]
external usenet poster
 
Posts: 6
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:19 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.