Forum Discussion
Check for overlap of Date and Time Formula
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.
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.
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
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.
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
