Check if time range is within another time range

**KOKOSEK** · 09-20-2021, 06:28 AM

	A	B	C	D
1			Result should be	formula
2	15:00-16:00	08:00-16:00	1	1
3	16:00-17:00	08:00-16:00	0	0
4	17:00-18:00	08:00-16:00	0	0
5
6			Result should be
7	15:00-16:00	17:00-01:00	0	0
8	16:00-17:00	17:00-01:00	0	0
9	17:00-18:00	17:00-01:00	1	1

	A	B	C	D
2	15:00-16:00	08:00-16:00	1	=LET( startTime1,FILTERXML("<x><y>"&SUBSTITUTE(A2,"-","</y><y>")&"</y></x>","//y[1]"), endTime1,FILTERXML("<x><y>"&SUBSTITUTE(A2,"-","</y><y>")&"</y></x>","//y[2]"), startTime2,FILTERXML("<x><y>"&SUBSTITUTE(B2,"-","</y><y>")&"</y></x>","//y[1]"), endTime2,FILTERXML("<x><y>"&SUBSTITUTE(B2,"-","</y><y>")&"</y></x>","//y[2]"), IF(endTime2>startTime2,IF(AND(startTime2<=startTime1,endTime2>=endTime1),1,0),IF(AND(startTime2<=startTime1,endTime2+"24:00:00">=endTime1),1,0)))
3	16:00-17:00	17:00-01:00	0	=LET( startTime1,FILTERXML("<x><y>"&SUBSTITUTE(A3,"-","</y><y>")&"</y></x>","//y[1]"), endTime1,FILTERXML("<x><y>"&SUBSTITUTE(A3,"-","</y><y>")&"</y></x>","//y[2]"), startTime2,FILTERXML("<x><y>"&SUBSTITUTE(B3,"-","</y><y>")&"</y></x>","//y[1]"), endTime2,FILTERXML("<x><y>"&SUBSTITUTE(B3,"-","</y><y>")&"</y></x>","//y[2]"), IF(endTime2>startTime2,IF(AND(startTime2<=startTime1,endTime2>=endTime1),1,0),IF(AND(startTime2<=startTime1,endTime2+"24:00:00">=endTime1),1,0)))

**Desgostoso** · 09-21-2021, 02:48 AM

Hi Kokosek,

Thank you so much for your reply!

I have taken the if statement and instead used LEFT RIGHT for the start and end time. I had to add IFERROR as well because when a cell is empty it throw me a #VALUE. This works like a charm.

There is only 1 issue I'm facing and that's that my formula is very long and I would need to check multiple rows with the same formula in 1 cell so I do a + and the same formula again changing the row numbers.

Example is attached.

This is the formula:

=IF(ISNUMBER(SEARCH("*Test*";$A$2));IF(IFERROR(VALUE(RIGHT(C$2;5))>VALUE(LEFT(C$2;5)););IF(IFERROR(AND(VALUE(LEFT(C$2;5))<=VALUE(LEFT($B13;5));VALUE(RIGHT(C$2;5))>=VALUE(RIGHT($B13;5))););1;0);IF(IFERROR(AND(VALUE(LEFT(C$2;5))<=VALUE(LEFT($B13;5));VALUE(RIGHT(C$2;5))+"24:00:00">=VALUE(RIGHT($B13;5))););1;0)))

And when I want to check the new row I add + and the same formula and change the row number on column A and C

+IF(ISNUMBER(SEARCH("*Test*";$A$3));IF(IFERROR(VALUE(RIGHT(C$3;5))>VALUE(LEFT(C$3;5)););IF(IFERROR(AND(VALUE(LEFT(C$3;5))<=VALUE(LEFT($B13;5));VALUE(RIGHT(C$3;5))>=VALUE(RIGHT($B13;5))););1;0);IF(IFERROR(AND(VALUE(LEFT(C$3;5))<=VALUE(LEFT($B13;5));VALUE(RIGHT(C$3;5))+"24:00:00">=VALUE(RIGHT($B13;5))););1;0)))

Is there any way to shorten this formula or how to repeat it without having to write the whole formula with a +