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