excel time range

K

kelvin_9

Active Member
Joined
Mar 6, 2015
Messages
444
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?

09:00-14:15HH14:15-18:30CR13:30-14:15AG14:15-21:30EE21:30-23:00DHL
09:00-18:30 ------------my request13:30-23:00 ------------my request

<colgroup><col><col></colgroup><tbody>
</tbody>

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

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
=LEFT(A10,5)&"-"&MID(A10,IF(RIGHT(A10,2)="CR",20,33),5)

caters for both formats
 
Upvote 0
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
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

<tbody>
</tbody>
Sheet1​

Worksheet Formulas
CellFormula
B1=LEFT($A1,5)&MID($A1,FIND(CHAR(1),SUBSTITUTE($A1,"-",CHAR(1),LEN($A1)-LEN(SUBSTITUTE($A1,"-","")))),6)

<tbody>
</tbody>

<tbody>
</tbody>



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?
thanks for advice

<tbody>
</tbody>

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
1(SUN)(MON)(TUE)(WED)(THU)(FRI)(SAT)(SUN)(MON)(TUE)(WED)(THU)(FRI)(SAT)
2Alex09:30-10:00
AA		13:30-21:45
AA		13:30-21:45
AA		09:30-10:00
AA		Alex09:30 - 19:0013:30 - 23:0013:30 - 23:0009:30 - 19:00
310:00-19:00
BB		21:45-22:15
BB		21:45-22:15
BB		10:00-19:00
BB		Bella09:00 - 18:3013:30 - 23:0011:00 - 20:3009:30 - 19:0009:00 - 18:30
422:15-23:00
CC		22:15-23:00
CC		Bethia09:00 - 18:3012:00 - 22:3013:00 - 22:3013:00 - 22:30
5Bella09:00-14:15
AA		13:30-14:15
AA		11:00-20:30
AA		09:30-14:15
AA		09:00-14:15
AA
614:15-18:30
BB		14:15-21:30
BB		14:15-19:00
BB		14:15-18:30
BB
721:30-23:00
CC
8Bethia09:00-09:30
AA		12:00-22:30
AA		13:00-22:30
AA		13:00-22:30
AA
909:30-18:30
BB

<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>
</tbody>
Sheet1​

Array Formulas
CellFormula
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"))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>



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?:eek:
 
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

J
  • Question
Calculate Hours Across Different Dates & Times
Replies
6
Views
907
Proulxs
P
K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
305
awoohaw
awoohaw
J
  • Solved
excel formula for if time exceeds 30 minutes then yes
Replies
2
Views
84
jquinlavin
J
F
  • Question
Excel Formula for Dynamic Timetable with Variable Time Increments based on certain hours of the day
Replies
1
Views
320
Muhammad_Usman
Muhammad_Usman
P
  • Question
**Add existing date & time to another cell with condition**
2
Replies
16
Views
290
psc1
P

Forum statistics

Threads
1,214,649
Messages
6,120,731
Members
448,987
Latest member
marion_davis

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