Extract values for earliest/latest times

Kpbeard

New Member
Joined
Jun 23, 2016
Messages
27
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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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
 
Upvote 0
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))
 
Upvote 0
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
 
Upvote 0
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: 14
Upvote 0
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
 
Upvote 0
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: 15
  • calculatedtimenavic2a.png
    calculatedtimenavic2a.png
    27 KB · Views: 14
Upvote 0
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
 
Upvote 0
Would it be possible for you to upload your version so I can see how the two differ? Thank you very much
 
Upvote 0

Forum statistics

Threads
1,212,931
Messages
6,110,745
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top