Time Constraints

[silentlenny07]

So I'm not sure how to solve my problem or what formula would be best:

I am copying a scanned image of a schedule from PDF, that I have recognized text on, to my excel product. I paste the data into certain cells and it should auto-calculate things for me. My issue comes from tracking the earliest and latest start and stop times for each round. All time is measured in 24-hour time for simplicity sake. Formulas and arrays I've tried are below and I have created a sample of my table below:

=MAX(IF($E$54:$E$75=1,$I$54:$I$75))
=MIN(IF($E$54:$E$75=1,$H$54:$H$75))
=MAX(IF($E$54:$E$75=2,$I$54:$I$75))
=MIN(IF($E$54:$E$75=2,$H$54:$H$75))
=MAX(IF($E$54:$E$75=3,$I$54:$I$75))
=MIN(IF($E$54:$E$75=3,$H$54:$H$75))

These arrays worked only if I manually typed each date into the cells. I did not work with the copy and paste function. I tried different formats, (time, text, general, and custom formats for 24-hour time 00\:00). When I paste the copy it renders it as general in the cells, I've tried after the copy to convert the format, to no avail. I've tried converting the PDF to word and then copying and pasting. I also copy with formatting to keep the cell spacing, which works well.

=TIME(LEFT(I54,2),RIGHT(I54,2),)
=RIGHT(H54,5)

I have no control over how the PDF comes or is created. I don't care where the results go on the sheet, but I will reference them on another sheet after the problem is solved. Any assistance would be greatly appreciated. Thank you in advance.

Sample Table
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Round[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Start[/TD]
[TD]Stop[/TD]
[TD] [/TD]
[TD]Sample Results[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]0900[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD]0900[/TD]
[TD]Earliest[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]0915[/TD]
[TD]1015[/TD]
[TD][/TD]
[TD]1015[/TD]
[TD]Latest[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]0910[/TD]
[TD]1010[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]0915[/TD]
[TD]1015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]1100[/TD]
[TD]1200[/TD]
[TD][/TD]
[TD]1100[/TD]
[TD]Earliest[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]1110[/TD]
[TD]1210[/TD]
[TD][/TD]
[TD]1215[/TD]
[TD]Latest[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]1115[/TD]
[TD]1215[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]1100[/TD]
[TD]1200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]1500[/TD]
[TD]1600[/TD]
[TD][/TD]
[TD]1500[/TD]
[TD]Earliest[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]1505[/TD]
[TD]1605[/TD]
[TD][/TD]
[TD]1645[/TD]
[TD]Latest[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]1500[/TD]
[TD]1600[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]1545[/TD]
[TD]1645[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[silentlenny07]

*I'm using Windows 10 and MS Excel 2013

 

[FormR]

Hi, welcome to the forum!

Your start and stop values are probably text - here is an alternative you can try that attempts to convert them to numbers.

Excel 2013/2016
	E	F	G	H	I	J	K	L
1	Round			Start	Stop
2	1			0900	1000		900	Earliest
3	1			0915	1015		1015	Latest
4	1			0910	1010
5	1			0915	1015
6	2			1100	1200		1100	Earliest
7	2			1110	1210		1215	Latest
8	2			1115	1215
9	2			1100	1200
10	3			1500	1600		1500	Earliest
11	3			1505	1605		1645	Latest
12	3			1500	1600
13	3			1545	1645
Sheet1

Cell Formulas
Range	Formula
K2	=AGGREGATE(15,6,(0+$H$2:$H$100)/($E$2:$E$100=1),1)
K3	=AGGREGATE(14,6,(0+$I$2:$I$100)/($E$2:$E$100=1),1)

 

[silentlenny07]

It works for rounds 1 and 2, couldn't figure out how to make it work for round 3. I'm getting a number error.

 

[FormR]

It works for rounds 1 and 2, couldn't figure out how to make it work for round 3.

Hi, you need to see if you can figure out what is different about either the round 3 values or there corresponding time values, for example do they have hidden spaces at the beginning or end?

If you can let us know, we may be able to suggest a workaround.

 

[silentlenny07]

=AGGREGATE(15,6,(0+$H$116:$H$140)/($E$116:$E$140=1),1)
=AGGREGATE(14,6,(0+$I$116:$I$140)/($E$116:$E$140=1),1)

Thank you for your help, these are the formulas that worked out well. I was gone for the holidays.