Extract values for earliest/latest times

[Kpbeard]

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.

i

 

[MuchToLearn]

I know this isn’t originally my problem, but I just want to see I can learn something by looking at how others are solving the problem.

I have pasted the data from post#5 and tried Kpbeard’s solution. Got it worked as expected after few adjustments: adjusted the data into the right range Kpbeard’s’s formula in G25 point too, and changed the times value in range A11:A21 to 24Hr equivalent… The formula is really long, I hope to wrap my mind around the whole logic, but couldn’t…

Now while I was making the adjustment, I noticed the time values of subsequent rows is always at 1/2-hour increment and each person only at 1 timeframe each day. I took advantage of those and tested another way which works in my copy. But this formula won’t this work without those above advantages.
ARRAY formula used in 'B25' cell (need completed by Ctrl+Shift+Enter also):

=IF(SUM(1*NOT(ISERR(FIND($A25,B$1:B$21))))=0,"",TEXT(((MATCH(TRUE,NOT(ISERR(FIND($A25,B$1:B$21))),0)-2)*0.5+8)/24,"HH:MM")&" - " & TEXT((((MATCH(TRUE,NOT(ISERR(FIND($A25,B$1:B$21))),0)-2)*0.5+8)+SUM(1*NOT(ISERR(FIND($A25,B$1:B$21))))*0.5)/24,"HH:MM")&"("&SUM(1*NOT(ISERR(FIND($A25,B$1:B$21))))*0.5&")")

Copy across and down.

 

[Kpbeard]

MuchToLearn, Many thanks for your formula. This version works across all cells in my workbook and I greatly appreciate the time you put into it. I have two questions, 1) how can I change it to 12 hour instead of 24 hour? 2) How can I add up the hours worked in each cell (B-F) to total them at the end of the row?

Thank you, Kpbeard

 

[MuchToLearn]

If it’s in the output range in B25: F28, change the “HH:MM” in the TEXT function to “H:MM AM/PM”. But if you mean the Time column in range A2:A21 need be in 12-hour format. Sorry, I can’t think of better answer than Kpbeard’s’ solution…(as said before about me changed time values in range A11:A21). Kpbeard’s’ solution is more robust, just I’m having hard time to understand, hope he would offer explanations…

For the total, I used at G25:

=SUM(1*NOT(ISERR(FIND($A25,$B$2:$F$21))))*0.5

The formula takes advantages of what I mentioned above again. Also array formula again needing Ctrl+Shift+Enter.

I really like to thank you for post the question and Kpbeard giving me the starting idea. Got bit more familiar with the formulas, especially the TEXT function, when finding the solution to this problem.

 

[Kpbeard]

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

I would appreciate anything you could provide with VBA. I've tried the other formulas, and although they are very good and I'm very grateful to those who provided their solutions, they are not working for what I'm trying to accomplish. I would like to explore the VBA approach and any help you can provide would be greatly appreciated.

 

[navic]

Would it be possible for you to upload your version so I can see how the two differ?

See this link for more details. I made a tutorial for you. There you have an example file to download to your own computer.

 

[Kpbeard]

See this link for more details. I made a tutorial for you. There you have an example file to download to your own computer.

Thank you very much. I have another question, why do the formulas stop working when I insert a row or two at the top of the sheet (rows 1 and/or 2)?

 

[MuchToLearn]

See this link for more details. I made a tutorial for you. There you have an example file to download to your own computer.

Very grateful you spending the time making the tutorial navic. Sorry about putting the wrong name in my previous 2 posts in this topic. I just realised I put the name Kpbeard when I really meant to referring to you. Hope there is a way to fix it, but I can’t reedit my posts anymore.
I would still have some questions about the whole formula. But better ask after I digested it a bit more…

 

[navic]

why do the formulas stop working when I insert a row or two at the top of the sheet (rows 1 and/or 2)?

If you have completed the task and subsequently added a few Rows.
There will be no change in certain ranges in the formula. You need to do these replacements manually.
I updated the tutorial. Please see the link.

 

[Kpbeard]

Let me rephrase my question. If I wanted to insert two rows (1 & 2), why do the formulas stop working correctly?
for example. As-is (

Cells referenced are $A$1:$A$21 & B$1:B$21

After I insert 2 rows 1 & 2

Cells change to $A$3:$A$23 & B$3:B$23

The cell ranges change like I would expect them to, but the values should not have changed. (notice the end time and # of hours) I'm perplexed as to why this is happening. Any ideas you have would be appreciated.

Thank you for all you do and I'm learning from the masters here in this forum. Much appreciated.

 

[navic]

I'm perplexed as to why this is happening.

In the first case we have a formula that returns the data from the last row for the condition.
So, notice that the last data of "Haleema" is in Row 19 (in the 'A' column it is the data 16:30 - 17:00)

The part of the formula that is set for the ROW argument is highlighted below

=INDEX($A$1:$A$21,MAX(ISNUMBER(SEARCH("*"&$A25&"*",B$1:B$21))*ROW(B$1:B$21)))

This ARRAY 'MAX' formula returns a value of 19, and the 'INDEX formula' looks for data in the nineteenth row.

If you add 2 rows above, then the whole range changes
- from $A$1:$A$21 to $A$3:$A$23
- from $B$1:$B$21 to $B$3:$B$23

Example

=INDEX($A$3:$A$23;MAX(ISNUMBER(SEARCH("*"&$A27&"*";B$3:B$23))*ROW(B$3:B$23)))

The 'MAX' function / formula now returns a value of 21 and that's fine because "Haleema" is in Row 21 (full rows on Excel Sheet).
But the range is wrong, because the data from Row 21 is now actually in Row 23 (If we look at Rows on the Excel Sheet without the range).
Now the 'INDEX formula' with respect to the range searches for data in Row 21.
In that case, it will return the data from the 'A' column (17:30 - 18:00).

So this formula is the reason, why you need to change the range.

Sorry but it's hard for me to explain it all in my bad English.
Use the 'F9' key to see the internal calculations of a particular formula argument.