Starting time and finishing time for overlapping time periods

[VickyBez]

Hi
I hope someone can assist.... I need to get the start and finish time of overlapping times.
For egsample my first columns time periods are:
8:00 - 10:00
12:00 - 14:00
16:00 - 18:00

The second column is 9:00 - 13:00.

I need to get the start and finish times where the first column's times fall within the second column ie. Answer would be:
9:00 - 10:00
12:00 - 13:00

Please help!!

 

[Peter_SSs]

Welcome to the MrExcel board!

Is this the sort of thing you are after?

22 07 03.xlsm
	A	B	C
1
2	8:00 - 10:00	9:00 - 13:00	9:00 - 10:00
3	12:00 - 14:00	9:00 - 13:00	12:00 - 13:00
4	16:00 - 18:00	9:00 - 13:00
5	16:00 - 18:00	16:30 - 17:20	16:30 - 17:20
Overlap

Cell Formulas
Range	Formula
C2:C5	C2	=LET(a,MAX(LEFT(A2,5),LEFT(B2,5)),b,MIN(RIGHT(A2,5),RIGHT(B2,5)),IF(a<b,TEXT(a,"h:mm - ")&TEXT(b,"h:mm"),""))

 

[VickyBez]

Welcome to the MrExcel board!

Is this the sort of thing you are after?

22 07 03.xlsm
	A	B	C
1
2	8:00 - 10:00	9:00 - 13:00	9:00 - 10:00
3	12:00 - 14:00	9:00 - 13:00	12:00 - 13:00
4	16:00 - 18:00	9:00 - 13:00
5	16:00 - 18:00	16:30 - 17:20	16:30 - 17:20
Overlap

Cell Formulas
Range	Formula
C2:C5	C2	=LET(a,MAX(LEFT(A2,5),LEFT(B2,5)),b,MIN(RIGHT(A2,5),RIGHT(B2,5)),IF(a<b,TEXT(a,"h:mm - ")&TEXT(b,"h:mm"),""))

 

[VickyBez]

Thank you so much! Can I please ask that you upload the sheet you attached... I am struggling to duplicate it on my side. I would really appreciate it if I could access your sheet and see where I am going wrong.

 

[Peter_SSs]

1. Start with a blank worksheet
2. Click this icon at the top left of my mini-sheet above.
   
   
3. Select cell A1 on your blank worksheet
4. Paste