find most early time and most late finish time for each unique job name

You have attached an image. That's not very easy to work with. We have to re-type your information before we can begin to address your problem. That will put a lot of people off completely. So...

Please attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

In Excel dates/times are just numbers formatted to look like dates/times.

So, all you need to do is find the min and max date/time for the job.

This example uses numbers but they could also be dates/times.

Data Range

	A	B	C	D	E	F	G
1	Job #	Start	End			Min Start	Max End
2	Job1	17	22		Job1	6	78
3	Job1	6	22		Job2	9	85
4	Job1	51	58		Job3	16	79
5	Job1	31	78
6	Job2	58	70
7	Job2	84	85
8	Job2	18	83
9	Job2	9	55
10	Job3	76	79
11	Job3	16	66
12	------	------	------	------	------	------	------

Enter this array formula** in F2:

=MIN(IF(A$2:A$11=E2,B$2:B$11))

Enter this array formula** in G2:

=MAX(IF(A$2:A$11=E2,C$2:C$11))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Select F2:G2 and copy down as needed.

You'll have to format the formula cells in the date/time style of your choice.

The request was NOT easy to follow. But, I think that the earliest start time refers (based on the JPG) to the TIME, not the date/time; so a straightforward MIN/IF would not return the correct answer. In this particular case (the orange shaded job), itwould - as all the start times were 18:30. If I'm correct, this array formula is needed:
=MIN(IF($A$2:$A$11=E2,MOD($B$2:$B$11,1)))

The latest finish time is REALLY confusing me!! Does it mean the longest time difference between starting and finishing... or does it mean something else. Please explain!! In the attached sheet, I have assumed that it means the longest duration. Also, I think I have REALLY over complicated the formula, so others - feel free to jump in.

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.