Check for overlap of Date and Time Formula

[glen302]

I am trying to find a formula to check overlap of Date and time in excel . I have a 2 worksheets each have columns of  start and Finish Date and Time Combined . I want to review each row  and give a yes if overlaps with a start or finish event in sheet 2 or no if I doesn't .  

[#2 · Accepted solution]

@glen302 Please have look at the attached workbook. I sorted the dat/time entries in sheet1 in ascending order. In sheet 2 I sorted the date/time entries in descending order.

Then on sheet1, I used XLOOKUP and INDEX/MATCH to find, for each start time the next largest starting time in sheet2 and compered it the ending time in sheet1. If it is less, then there is an overlap (TRUE) otherwise not (FALSE). If I have misunderstood, please give a few examples of entries that you consider to be overlapping and explain how you come to that conclusion.

Note that the ending dates/times in sheet1 were texts, so I converted them to real dates first using Text.to-columns.

[#3]

@Riny_van_Eekelen  Thanks so much for your help , I would never have worked this out .  

This certainly shows if there was an overlap,  I would also like it to say true if is within a start and finish date and  time  event in sheet 2  is there a way i can add this to formula or add a column start a separate formula ?   

I suppose what I'm asking is does an event in Sheet 1 occur  in or overlap with  the date and time event  on sheet 2 

[#4]

@glen302 I believe that is what my suggestion did. Show events from sheet1 that overlap events in sheet2. So, not sure what you mean by your last question. Can you find a type of overlap that doesn't not get picked-up by my formula and describe the logic of it. In other words, what steps did you go through in your mind to determine that this particular event is overlapping another. Then, a formula will not be all that difficult, I guess.

[#5]

@Riny_van_Eekelen 

your formula hits the over lap perfectly but i need  another  formula to see if the start and finish date time event in sheet one  sits in between  a start and finish time in sheet 2 .

I tired and couldn't get it to work I have included a snip on sheet one that highlights the scenario i want the formula to capture  .

thanks for your help and once I get over the amount data i have to  analysis  im going to spend some time learning about Xlookup . it look much more useful then Vlookup

[#6]

@glen302 See attached. Would that work?

[#7]

@Riny_van_Eekelen  

yep that works great .   every scenario covered now thanks for help  

[#8]

@glen302  You're welcome!