• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Date Arrays - Checking for Overlaps

J

Jyrke

New Member
I'm needing to compare one date range to see if it overlaps at all with another date range.
I found this excellent thread posted on this site, describing the basic formula, but for the life of me I can't seem to make this work for use with arrays:
https://chandoo.org/wp/date-overlap-formulas/

Attached is an example of the problem. Essentially I have a set of flight data files and I need to identify the flights that they overlap with, if any.
Each flight data file has a start and end time. Each flight has a reference number, start and end time.

Based on the article referenced above, I believe I have the logic to check if there is an overlap (see formula shown in J1), and also how to determine the corresponding flight data row (formula in J2). The logic works if I'm just checking against a single row, but I just can't seem to get it to work for arrays.

Any help would be MOST appreciated - been trying to solve this for 2 days now.
Apologies in advance if I'm not posting this correctly - it's my first post ever (to any forum!)

THANKS!
 

Attachments

  • Dare Arrays - Overlap Check.xlsx
    9.1 KB · Views: 3
Code: 
= OR((@start<Flight_End_Dates)*(@end>Flight_Start_Dates))

= MIN(IF((@start<Flight_End_Dates)*(@end>Flight_Start_Dates), FlightRef))

73507
 

Attachments

  • Dare Arrays - Overlap Check.xlsx
    20.9 KB · Views: 7
Peter, thanks so much for your time. This works perfectly and has saved me a lot of grief. I was so far off, I'll definitely be dissecting your answer to learn more. Very much appreciate you & all others who so willing share your expertise!
 
My pleasure.
To help with the dissection, the starting point is Chandoo's formula for 'no overlap' but reversed using NOT
= NOT(OR(y<a, x>b))
This reduces to
= AND(y>a, x<b)
but, since x and y are arrays, AND is replaced by multiplication, to give
= (y>a)*(x<b)
There is also a change that would be needed if text Flight References appeared in place of the numeric data. In such a case, MIN could not be used to return a number from a list and
= TEXTJOIN(", ", , IF((@start<Flight_End_Dates)*(@end>Flight_Start_Dates), FlightRef, ""))
would be more appropriate.
73517
 
I was just starting to look into how to handle text flight references so you read my mind!
This is all very helpful and completely understandable - thank you!!
 
You must log in or register to reply here.
Back
Top