excel time range

K

kelvin_9

Active Member
Joined
Mar 6, 2015
Messages
460
Office Version
  1. 2019
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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
On the assumption that the last textual component could be anything and not just CR/DHL and that there may be multiple time periods:


Book1
AB
109:00-14:15HH14:15-18:30CR09:00-18:30
213:30-14:15AG14:15-21:30EE21:30-23:00DHL13:30-23:00
308:15-22:00EE08:15-22:00
407:00-09:00AA09:00-14:00BB14:00-17:00CCC17:00-23:30DHL07:00-23:30
Sheet1
Cell Formulas
RangeFormula
B1=LEFT($A1,5)&MID($A1,FIND(CHAR(1),SUBSTITUTE($A1,"-",CHAR(1),LEN($A1)-LEN(SUBSTITUTE($A1,"-","")))),6)


WBD
 
Upvote 0
wideboydixon said:
On the assumption that the last textual component could be anything and not just CR/DHL and that there may be multiple time periods:

AB
09:00-14:15HH14:15-18:30CR09:00-18:30
13:30-14:15AG14:15-21:30EE21:30-23:00DHL13:30-23:00
08:15-22:00EE08:15-22:00
07:00-09:00AA09:00-14:00BB14:00-17:00CCC17:00-23:30DHL07:00-23:30

<tbody>
[TD="align: center"]1[/TD]

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

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

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

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B1[/TH]
[TD="align: left"]=LEFT($A1,5)&MID($A1,FIND(CHAR(1),SUBSTITUTE($A1,"-",CHAR(1),LEN($A1)-LEN(SUBSTITUTE($A1,"-","")))),6)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



WBD
Click to expand...
wideboydixon, you made my day and it's great for me
one more question, if i had few buddy's roster which need to return with the earliest start time and latest end time
what should i do then?[TABLE="width: 798"]
<tbody>[TR]
[TD]thanks for advice[/TD]
[/TR]
</tbody>[/TABLE]

https://drive.google.com/open?id=1aAkj1FxvL0YE3qiVr-eZJ1S6BrKbmXlN
 
Last edited:
Upvote 0

Book1
ABCDEFGHIJKLMNOPQRS
1(SUN)(MON)(TUE)(WED)(THU)(FRI)(SAT)(SUN)(MON)(TUE)(WED)(THU)(FRI)(SAT)
2Alex09:30-10:00 AA13:30-21:45 AA13:30-21:45 AA09:30-10:00 AAAlex 09:30 - 19:0013:30 - 23:0013:30 - 23:0009:30 - 19:00
310:00-19:00 BB21:45-22:15 BB21:45-22:15 BB10:00-19:00 BBBella09:00 - 18:3013:30 - 23:0011:00 - 20:3009:30 - 19:0009:00 - 18:30
422:15-23:00 CC22:15-23:00 CCBethia09:00 - 18:3012:00 - 22:3013:00 - 22:3013:00 - 22:30
5Bella09:00-14:15 AA13:30-14:15 AA11:00-20:30 AA09:30-14:15 AA09:00-14:15 AA
614:15-18:30 BB14:15-21:30 BB14:15-19:00 BB14:15-18:30 BB
721:30-23:00 CC
8Bethia09:00-09:30 AA12:00-22:30 AA13:00-22:30 AA13:00-22:30 AA
909:30-18:30 BB
Sheet1
Cell Formulas
RangeFormula
M2{=IF(OFFSET(B$2,ROWS($L$2:$L2)*3-3,0)="","",TEXT(MIN(IFERROR(TIMEVALUE(LEFT(OFFSET(B$2:B$4,ROWS($L$2:$L2)*3-3,0),5)),1)),"hh:mm")&" - "&TEXT(MAX(IFERROR(TIMEVALUE(MID(OFFSET(B$2:B$4,ROWS($L$2:$L2)*3-3,0),7,5)),0)),"hh:mm"))}
Press CTRL+SHIFT+ENTER to enter array formulas.


Copy formula in M2 across and down as necessary.

WBD
 
Upvote 0
wideboydixon said:
ABCDEFGHIJKLMNOPQRS
AlexAlex09:30 - 19:0013:30 - 23:0013:30 - 23:0009:30 - 19:00
Bella09:00 - 18:3013:30 - 23:0011:00 - 20:3009:30 - 19:0009:00 - 18:30
Bethia09:00 - 18:3012:00 - 22:3013:00 - 22:3013:00 - 22:30
Bella
Bethia

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><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: center"][/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: center"][/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: center"][/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: center"]09:00-14:15
AA[/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"]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: center"]14:15-18:30
BB[/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"]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: 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"]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: 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"]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: 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]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]M2[/TH]
[TD="align: left"]{=IF(OFFSET(B$2,ROWS($L$2:$L2)*3-3,0)="","",TEXT(MIN(IFERROR(TIMEVALUE(LEFT(OFFSET(B$2:B$4,ROWS($L$2:$L2)*3-3,0),5)),1)),"hh:mm")&" - "&TEXT(MAX(IFERROR(TIMEVALUE(MID(OFFSET(B$2:B$4,ROWS($L$2:$L2)*3-3,0),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]



Copy formula in M2 across and down as necessary.

WBD
Click to expand...

THANKS WBD
however, what's wrong with yellow? what should i need to modify?:eeek:
 
Upvote 0
In your example you were using 3 rows for each person so my formula assumes you have three rows per person.

WBD
 
Upvote 0
wideboydixon said:
In your example you were using 3 rows for each person so my formula assumes you have three rows per person.

WBD
Click to expand...

for instance, what if i had 10 members in one sheet and maximum had 4 rows for some members and minimum 1 row for some members?
is it possible to make it work or using macro?

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


Book1
ABCDEFGHIJKLMNOPQRS
1(SUN)(MON)(TUE)(WED)(THU)(FRI)(SAT)(SUN)(MON)(TUE)(WED)(THU)(FRI)(SAT)
2Alex09:30-10:00 AA13:30-21:45 AA13:30-21:45 AA09:30-10:00 AAAlex 09:30 - 19:0013:30 - 23:0013:30 - 23:0009:30 - 19:00
310:00-19:00 BB21:45-22:15 BB21:45-22:15 BB10:00-19:00 BBBella09:00 - 18:3013:30 - 23:0011:00 - 20:3009:30 - 19:0009:00 - 18:30
422:15-23:00 CC22:15-23:00 CCBethia09:00 - 18:3012:00 - 22:3013:00 - 22:3013:00 - 22:30
5Bella09:00-14:15 AA13:30-14:15 AA11:00-20:30 AA09:30-14:15 AA09:00-14:15 AAChelsea09:00 - 18:3009:30 - 19:0009:30 - 20:0009:30 - 19:00
614:15-18:30 BB14:15-21:30 BB14:15-19:00 BB14:15-18:30 BBCherries09:30 - 17:30
721:30-23:00 CCCherry12:00 - 23:0011:00 - 21:0009:30 - 20:00
8Bethia09:00-09:30 AA12:00-22:30 AA13:00-22:30 AA13:00-22:30 AAEnd
909:30-18:30 BB
10Chelsea09:00-09:30 AA09:30-14:15 AA09:30-10:00 AA09:30-19:00 AA
1109:30-18:30 BB14:15-19:00 BB10:00-20:00 BB
12Cherries09:30-17:30 AAA
13Cherry12:00-21:45 AA11:00-21:00 AA09:30-10:00 AA
1421:45-22:15 BB10:00-20:00 BB
1522:15-23:00 CC
16End
Sheet1
Cell Formulas
RangeFormula
M2{=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"))}
Press CTRL+SHIFT+ENTER to enter array formulas.


The formula is also now quite complex :)

WBD
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Count entry by time
Replies
3
Views
194
admvlad
A
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
316
dshtesy
D
tezza
  • Solved
Autosum specific rows and columns
Replies
2
Views
270
tezza
tezza
S
  • Solved
Formula to split date and time and work out energy usage trend
Replies
6
Views
460
Sundance_Kid
S
J
  • Question Question
Calculate Hours Across Different Dates & Times
Replies
6
Views
1K
Proulxs
P

Forum statistics

Threads
1,223,888
Messages
6,175,203
Members
452,617
Latest member
Narendra Babu D

Share this page

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