Extract values for earliest/latest times

Kpbeard

New Member
Joined
Jun 23, 2016
Messages
20
I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the use of helper cells. I've not been able to find the right mixture of functions to do this yet. Any help would be greatly appreciated. Sample image of columns A & B and what I'm trying to achieve.
Thank you to anyone who responds.
1603295937580.png
i
1603295981389.png
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,768
Without helper columns, I know of no function that can parse the text into times in an array. A VBA User Defined Function would be best for this. I know how to do that, but you have pasted an image of your data and not anything I can copy and paste into Excel. Please try using XL2BB
 

Kpbeard

New Member
Joined
Jun 23, 2016
Messages
20
Without helper columns, I know of no function that can parse the text into times in an array. A VBA User Defined Function would be best for this. I know how to do that, but you have pasted an image of your data and not anything I can copy and paste into Excel. Please try using XL2BB
Copy Help Desk Schedule Example.xlsx
C
458:30 - 9:00
Schedule Example
Cell Formulas
RangeFormula
C45C45=INDEX($A$2:$A$23,SUMPRODUCT(MAX(ROW($B$2:$B$23)*($A45=$B$2:$B$23))-1))
 

Kpbeard

New Member
Joined
Jun 23, 2016
Messages
20

ADVERTISEMENT

Please let me know if this works since it's the first time using XL2BB.
Sorry, thank you for offering to help solve my issue. I think I messed up the first time pasting XL2BB so I'm going to try again.

Copy Help Desk Schedule Example.xlsx
ABCDEFG
2
3TIMEMondayTuesdayWednesdayThursdayFriday
48:00 - 8:30Tre'VaughnKeith/GijsTre'VaughnTre'VaughnKeith/Tre'Vaughn
58:30 - 9:00Tre'VaughnKeith/GijsTre'VaughnTre'Vaughn/GijsKeith/Tre'Vaughn
69:00 - 9:30Tre'Vaughn/GijsKeith/GijsTre'Vaughn/GijsTre'Vaughn/GijsKeith/Tre'Vaughn
79:30 - 10:00Tre'Vaughn/GijsKeith/GijsTre'Vaughn/GijsTre'Vaughn/GijsKeith/Tre'Vaughn
810:00 - 10:30Tre'Vaughn/GijsKeith/GijsTre'Vaughn/GijsTre'Vaughn/GijsKeith/Tre'Vaughn
910:30 - 11:00Tre'Vaughn/GijsKeith/GijsTre'Vaughn/GijsTre'Vaughn/GijsKeith/Tre'Vaughn
1011:00 - 11:30Tre'Vaughn/GijsKeith/GijsTre'Vaughn/GijsTre'Vaughn/GijsKeith/Tre'Vaughn
1111:30 - 12:00Tre'Vaughn/GijsKeith/GijsTre'Vaughn/GijsTre'Vaughn/GijsKeith/Tre'Vaughn
1212:00 - 12:30GijsKeith/Tre'VaughnGijsGijsKeith/Gijs
1312:30 - 1:00GijsKeith/Tre'VaughnGijsKeith/Gijs
141:00 - 1:30HaleemaHaleema/Tre'VaughnHaleemaHaleemaHaleema/Gijs
151:30 - 2:00HaleemaHaleema/Tre'VaughnHaleemaHaleemaHaleema/Gijs
162:00 - 2:30HaleemaHaleema/Tre'VaughnHaleemaHaleemaHaleema/Gijs
172:30 - 3:00HaleemaHaleema/Tre'VaughnHaleemaHaleemaHaleema/Gijs
183:00 - 3:30HaleemaHaleema/Tre'VaughnHaleemaHaleemaHaleema/Gijs
193:30 - 4:00HaleemaHaleema/Tre'VaughnHaleemaHaleemaHaleema/Gijs
204:00 - 4:30HaleemaHaleemaHaleemaHaleemaHaleema
214:30 - 5:00HaleemaHaleemaHaleemaHaleemaHaleema
225:00 - 5:30ClosedClosedClosedClosedClosed
235:30 - 6:00ClosedClosedClosedClosedClosed
24
25This is what I'm trying to accomplish
26MondayTuesdayWednesdayThursdayFridayTotal Hrs
27Haleema1 pm - 5 pm (4)1 pm - 5 pm (4)1 pm - 5 pm (4)1 pm - 5 pm (4)1 pm - 5 pm (4)20
28Gijs9 am – 1 pm (4)8 am – 12 pm (4)9 am – 1 pm (4)8:30 am – 12:30 pm (4)12 am – 4 pm (4)20
29Tre'Vaughn8 am - 12 pm (4)12 am – 4 pm (4)8 am - 12 pm (4)8 am - 12 pm (4)8 am - 12 pm (4)20
30Keith8 am - 1 pm (5)8 am - 1 pm (5)10
Schedule Example
 

navic

Active Member
Joined
Jun 14, 2015
Messages
271
Office Version
  1. 2013
Platform
  1. Windows
Try this formulas below (Maybe it will help you in the direction of solving the task.
These formulas were created for European time.
You should convert the time to AM / PM.

In the 'B25' cell put ARRAY formula below (The formula is completed by Ctrl+Shift+Enter and copy across)
Code:
=IFERROR(TEXT(TRIM(IF(MID(INDEX($A$1:$A$21,MATCH(TRUE,INDEX(ISNUMBER(SEARCH($A25,B$1:B$21)),0),0)),2,5)=" -",LEFT(INDEX($A$1:$A$21,MATCH(TRUE,INDEX(ISNUMBER(SEARCH($A25,B$1:B$21)),0),0)),5),LEFT(INDEX($A$1:$A$21,MATCH(TRUE,INDEX(ISNUMBER(SEARCH($A25,B$1:B$21)),0),0)),5)))*1,"hh:mm")&" - "&TEXT(TRIM(IFERROR(RIGHT(INDEX($A$1:$A$21,MAX(ISNUMBER(SEARCH("*"&$A25&"*",B$1:B$21))*ROW(B$1:B$21))),LEN(INDEX($A$1:$A$21,MAX(ISNUMBER(SEARCH("*"&$A25&"*",B$1:B$21))*ROW(B$1:B$21))))-FIND("- ",INDEX($A$1:$A$21,MAX(ISNUMBER(SEARCH("*"&$A25&"*",B$1:B$21))*ROW(B$1:B$21))))),""))*1,"hh:mm")&" "&"("&TEXT(TRIM(IFERROR(RIGHT(INDEX($A$1:$A$21,MAX(ISNUMBER(SEARCH("*"&$A25&"*",B$1:B$21))*ROW(B$1:B$21))),LEN(INDEX($A$1:$A$21,MAX(ISNUMBER(SEARCH("*"&$A25&"*",B$1:B$21))*ROW(B$1:B$21))))-FIND("- ",INDEX($A$1:$A$21,MAX(ISNUMBER(SEARCH("*"&$A25&"*",B$1:B$21))*ROW(B$1:B$21))))),""))*1-TRIM(IF(MID(INDEX($A$1:$A$21,MATCH(TRUE,INDEX(ISNUMBER(SEARCH($A25,B$1:B$21)),0),0)),2,5)=" -",LEFT(INDEX($A$1:$A$21,MATCH(TRUE,INDEX(ISNUMBER(SEARCH($A25,B$1:B$21)),0),0)),5),LEFT(INDEX($A$1:$A$21,MATCH(TRUE,INDEX(ISNUMBER(SEARCH($A25,B$1:B$21)),0),0)),5)))*1,"hh:mm")&")","")
In the 'G25' cell put formula below (copy down)
Code:
=IFERROR(LEFT(RIGHT(B25,6),5)*1,0)+IFERROR(LEFT(RIGHT(C25,6),5)*1,0)+IFERROR(LEFT(RIGHT(D25,6),5)*1,0)+IFERROR(LEFT(RIGHT(E25,6),5)*1,0)+IFERROR(LEFT(RIGHT(F25,6),5)*1,0)
 

Attachments

  • calculatedtimenavic.png
    calculatedtimenavic.png
    14.6 KB · Views: 13

Kpbeard

New Member
Joined
Jun 23, 2016
Messages
20

ADVERTISEMENT

Navic, Thank you very much for the formula. I copied the array into B25 (Ctrl+Shift+Enter) and copied across. This worked well for everything in row 25. When I went to copy down to row 28 I'm not getting any results and all cells are showing as arrays. I also copied the formula to G25 and it only sums to 1. How do you have your formatting set up for those cells?

Thank you again for your help, I greatly appreciate you.

Keith
 

navic

Active Member
Joined
Jun 14, 2015
Messages
271
Office Version
  1. 2013
Platform
  1. Windows
All 'B25:F28' cells are formatted as general. In these cells there is text as a result and the alignment is to the left.
All 'G25:G28' cells are formatted as time.
 

Attachments

  • calculatedtimenavic2.png
    calculatedtimenavic2.png
    25.4 KB · Views: 11
  • calculatedtimenavic2a.png
    calculatedtimenavic2a.png
    27 KB · Views: 10

Kpbeard

New Member
Joined
Jun 23, 2016
Messages
20
Navic, Thank you for your response. So far I'm good with the formatting, just cannot get the same results you posted for all names in column A.

Keith
 

Kpbeard

New Member
Joined
Jun 23, 2016
Messages
20
Would it be possible for you to upload your version so I can see how the two differ? Thank you very much
 

Watch MrExcel Video

Forum statistics

Threads
1,113,833
Messages
5,544,574
Members
410,621
Latest member
S Oberlander
Top