excel time range

[kelvin_9]

hello all,
i'd like to ask anyway(formula / macro)that i can given out the best START time and END time in a range like these?

[TABLE="width: 735"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]09:00-14:15HH14:15-18:30CR[/TD]
[TD]13:30-14:15AG14:15-21:30EE21:30-23:00DHL[/TD]
[/TR]
[TR]
[TD]09:00-18:30 ------------my request[/TD]
[TD]13:30-23:00 ------------my request[/TD]
[/TR]
</tbody>[/TABLE]

thanks guys and hope someone can point me out in right way

 

[kelvin_9]

To enable a formula with variable rows, I had to add an extra row at the end of each list like this:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
Alex	Alex			09:30 - 19:00	13:30 - 23:00	13:30 - 23:00	09:30 - 19:00
Bella		09:00 - 18:30	13:30 - 23:00	11:00 - 20:30		09:30 - 19:00	09:00 - 18:30
Bethia	09:00 - 18:30	12:00 - 22:30		13:00 - 22:30	13:00 - 22:30
Bella	09:00-14:15 AA	Chelsea		09:00 - 18:30	09:30 - 19:00	09:30 - 20:00			09:30 - 19:00
14:15-18:30 BB	Cherries						09:30 - 17:30
Cherry			12:00 - 23:00	11:00 - 21:00	09:30 - 20:00
Bethia	End
Chelsea	09:30-19:00 AA
Cherries
Cherry
End

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: center"](SUN)[/TD]
[TD="align: center"](MON)[/TD]
[TD="align: center"](TUE)[/TD]
[TD="align: center"](WED)[/TD]
[TD="align: center"](THU)[/TD]
[TD="align: center"](FRI)[/TD]
[TD="align: center"](SAT)[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"](SUN)[/TD]
[TD="align: center"](MON)[/TD]
[TD="align: center"](TUE)[/TD]
[TD="align: center"](WED)[/TD]
[TD="align: center"](THU)[/TD]
[TD="align: center"](FRI)[/TD]
[TD="align: center"](SAT)[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]09:30-10:00
AA[/TD]
[TD="align: center"]13:30-21:45
AA[/TD]
[TD="align: center"]13:30-21:45
AA[/TD]
[TD="align: center"]09:30-10:00
AA[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]10:00-19:00
BB[/TD]
[TD="align: center"]21:45-22:15
BB[/TD]
[TD="align: center"]21:45-22:15
BB[/TD]
[TD="align: center"]10:00-19:00
BB[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]22:15-23:00
CC[/TD]
[TD="align: center"]22:15-23:00
CC[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: center"][/TD]
[TD="align: center"]09:00-14:15
AA[/TD]
[TD="align: center"]13:30-14:15
AA[/TD]
[TD="align: center"]11:00-20:30
AA[/TD]
[TD="align: center"][/TD]
[TD="align: center"]09:30-14:15
AA[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]14:15-18:30
BB[/TD]
[TD="align: center"]14:15-21:30
BB[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]14:15-19:00
BB[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]21:30-23:00
CC[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]09:00-09:30
AA[/TD]
[TD="align: center"]12:00-22:30
AA[/TD]
[TD="align: center"][/TD]
[TD="align: center"]13:00-22:30
AA[/TD]
[TD="align: center"]13:00-22:30
AA[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: center"]09:30-18:30
BB[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: center"][/TD]
[TD="align: center"]09:00-09:30
AA[/TD]
[TD="align: center"]09:30-14:15
AA[/TD]
[TD="align: center"]09:30-10:00
AA[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]09:30-18:30
BB[/TD]
[TD="align: center"]14:15-19:00
BB[/TD]
[TD="align: center"]10:00-20:00
BB[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]09:30-17:30
AAA[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]12:00-21:45
AA[/TD]
[TD="align: center"]11:00-21:00
AA[/TD]
[TD="align: center"]09:30-10:00
AA[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]21:45-22:15
BB[/TD]
[TD="align: center"][/TD]
[TD="align: center"]10:00-20:00
BB[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]22:15-23:00
CC[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]M2[/TH]
[TD="align: left"]{=IF(INDEX(B:B,MATCH($L2,$A:$A,0))="","",TEXT(MIN(IFERROR(TIMEVALUE(LEFT(INDEX(B:B,MATCH($L2,$A:$A,0)):INDEX(B:B,MATCH($L3,$A:$A,0)-1),5)),1)),"hh:mm")&" - "&TEXT(MAX(IFERROR(TIMEVALUE(MID(INDEX(B:B,MATCH($L2,$A:$A,0)):INDEX(B:B,MATCH($L3,$A:$A,0)-1),7,5)),0)),"hh:mm"))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



The formula is also now quite complex

WBD

YOU ARE AWESOME WBD!!! THANKS FOR THE GREAT WORK AND DEEPLY APPRECIATE
can it run by record macro? i tired and turn to error 1004

 

[wideboydixon]

It's a formula; changing the cell contents on the left will automatically update the content on the right. There's no need to run a macro - it's constantly "live".

WBD

 

[kelvin_9]

It's a formula; changing the cell contents on the left will automatically update the content on the right. There's no need to run a macro - it's constantly "live".

WBD

i want to run a macro because of if i clear content the data and paste another new data for my crew, the formula will be incorrect(#REF)

 

[wideboydixon]

It will break if you delete rows or columns I'm sure. Pasting data over the top shouldn't make any difference.

WBD

 

[kelvin_9]

It will break if you delete rows or columns I'm sure. Pasting data over the top shouldn't make any difference.

WBD

this is BRILLIANT and thanks for that, save my time, WBD
understood that it will break if i delete it or clear content by right click
however i found if the second week crew member less than first week, the last row of my crew member shouldn't show up normal(00:00-00:00)

on the other contrary, i want to use macro which is seems to be accuracy & safety just precaution no one break my workbook by any mistake
known that there is limited character on array formula at macro, so i am reading more and more article to help/solve on it hopefully

i am also thinking to setup my crew member database as table in column A2, then use "sumifs+index+match" to look up sequentially:
date, name, start time, end time

will it be easier?

 

[kelvin_9]

should be sumif+index+match, likes:
https://drive.google.com/open?id=1tW7tq4qq2v8QVI9xvLvuFAtTwFrHdlnt

 

[wideboydixon]

I think we're done on this thread; I suggest you start a new thread for a new question.

WBD