Check if time range is within another time range

Hello dear people!

I would like to ask your help with the following problem.

I want to check if a time range as example below is within another time range, this also needs to pass midnight as you can see.

Example 1:

A	B	C Result
15:00-16:00	08:00-16:00	1
16:00-17:00	08:00-16:00	0
17:00-18:00	08:00-16:00	0

Example 2:

A	B	C Result
15:00-16:00	17:00-01:00	0
16:00-17:00	17:00-01:00	0
17:00-18:00	17:00-01:00	1

I appreciate your help!

Thank you,

I missed that Attached the example file

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 +

Hi AliGW,

Yes checked post #5 as well. And thanks a lot for taking the time to check my question kvsrinivasamurthy! highly appreciated.

I don't think will work sadly, I tried I need to check a range of hours. Can refer to the attachment I put under #6

Thanks again!

Those were 2 examples I gave to cover normal hours and after midnight hours. So I could start to work with something. If I need a new thread for this question I posted in #6 I will and mark this as resolved. As they did resolve the issue I gave

Okay. then I'll leave it like this and see KOKOSEK or kvsrinivasamurthy would still want to have a look at my post #6

Fingers crossed :D I've been struggling with this for a week

Hi kvsrinivasamurthy,

My example is in post #6

So what I basically am looking for is to see which hours are covered. For example IF in A2 the word "Test" appears I want it to check the corresponding cell, in this case C3. If C3 is covering the hour 08:00-09:00 in B13 then it should show 1.

As you see in A3 there is no "Test" written but "Different" so it doesn't count it.

Problem is (at least I don't know how) that the formula will be too long if I repeat the formula 30+ times in 1 cell. it will go over the character limit.

I took IF(endTime2>startTime2,IF(AND(startTime2<=startTime1,endTime2>=endTime1),1,0),IF(AND(startTime2<=startTime1,endTime2+"24:00:00">=endTime1),1,0)) from KOKOSEK in Post: #4

I hope it's a bit more clear

Hi, kvsrinivasamurthy

No sorry, I gave you a better example now. Just imagine now there are 40 more rows that the same needs to be checked for.

Then the formula will be too long to put it in 1 cell.

Hi see attached.

I hope it's clear. The formula works. But I need to shorten it or find a different way to combine the same formula in 1 cell and not reach the limit of 9000~ characters

Hi JeteMc

This is amazing!, I didn't think about an array myself. I tried and it works only after midnight hours don't work

For example, in your file you attached if in C10 you put 16:00-24:00 it works perfectly. But when you put for example 18:00-02:00 or anything else after midnight it doesn't work.

I would have to check the formula itself I think, if you or someone else sees the solution to that I would highly appreciate it

Thanks so much!

Thanks works!