Google-sheets – Examine for overlap in Google Sheets

formulasgoogle sheetsworksheet-function

Let's say I have the following start and end days/times:

enter image description here SHEET1

Resulting in a range of time from Mon 10am through Tues at 11:59pm.

I'd like to know whether this range of time overlaps another range of time on a separate sheet.

Below is an example of 2 date ranges to compare to: one that overlaps and one that doesn't:

enter image description here SHEET2

I'd like the function in the row associated with OVERLAP? to return either TRUE or FALSE depending on whether there is overlap in the given time range with the range shown for SHEET1.

Is it possible to construct date ranges based on start/end times and then compare that range to other ranges of time to determine if there's overlap?

  • Preferably all on the fly (i.e., not saving any outputs to a sheet until the final TRUE/FALSE.

Sample Sheet (as comma delimited):

Start Day,  Monday, 
Start Time, 10:00 AM,   
End Day,    Tuesday,
End Time,   11:59 PM

Day of interest,    Monday,     Tuesday,
DOI start,          8:00 AM,    8:00 AM,
DOI end,            9:00 AM,    9:00 AM,    
OVERLAP?,           FALSE,      TRUE

Best Answer

the spreadsheet doesn't understand the "advanced" human logic from a go like connections between events and comparing the overlap of their durations... therefore Sheet1!B1 needs to be converted into date format corresponding to same day (in present, past or future - reference doesn't matter) with appended time from Sheet1!B2 which is then compared to Sheet2!B1 with appended Sheet2!B2 etc.

simply said, formula does this:

if Monday 8:00 AM > or = to Monday 10:00 AM and Monday 9:00 AM < than Tuesday 11:59 PM statement is TRUE otherwise its FALSE like in this case

=AND(IF(B1         =TEXT(DATE(2018; 1; 1); "dddd"); DATE(2018; 1; 1); 
     IF(B1         =TEXT(DATE(2018; 2; 1); "dddd"); DATE(2018; 2; 1); 
     IF(B1         =TEXT(DATE(2018; 3; 1); "dddd"); DATE(2018; 3; 1); 
     IF(B1         =TEXT(DATE(2018; 4; 1); "dddd"); DATE(2018; 4; 1); 
     IF(B1         =TEXT(DATE(2018; 5; 1); "dddd"); DATE(2018; 5; 1); 
     IF(B1         =TEXT(DATE(2018; 6; 1); "dddd"); DATE(2018; 6; 1); 
     IF(B1         =TEXT(DATE(2018; 7; 1); "dddd"); DATE(2018; 7; 1); )))))))&B2 >=
     IF(Sheet1!$B$1=TEXT(DATE(2018; 1; 1); "dddd"); DATE(2018; 1; 1); 
     IF(Sheet1!$B$1=TEXT(DATE(2018; 2; 1); "dddd"); DATE(2018; 2; 1); 
     IF(Sheet1!$B$1=TEXT(DATE(2018; 3; 1); "dddd"); DATE(2018; 3; 1); 
     IF(Sheet1!$B$1=TEXT(DATE(2018; 4; 1); "dddd"); DATE(2018; 4; 1); 
     IF(Sheet1!$B$1=TEXT(DATE(2018; 5; 1); "dddd"); DATE(2018; 5; 1); 
     IF(Sheet1!$B$1=TEXT(DATE(2018; 6; 1); "dddd"); DATE(2018; 6; 1); 
     IF(Sheet1!$B$1=TEXT(DATE(2018; 7; 1); "dddd"); DATE(2018; 7; 1); )))))))&Sheet1!$B$2; 
     IF(B1         =TEXT(DATE(2018; 1; 1); "dddd"); DATE(2018; 1; 1); 
     IF(B1         =TEXT(DATE(2018; 2; 1); "dddd"); DATE(2018; 2; 1); 
     IF(B1         =TEXT(DATE(2018; 3; 1); "dddd"); DATE(2018; 3; 1); 
     IF(B1         =TEXT(DATE(2018; 4; 1); "dddd"); DATE(2018; 4; 1); 
     IF(B1         =TEXT(DATE(2018; 5; 1); "dddd"); DATE(2018; 5; 1); 
     IF(B1         =TEXT(DATE(2018; 6; 1); "dddd"); DATE(2018; 6; 1); 
     IF(B1         =TEXT(DATE(2018; 7; 1); "dddd"); DATE(2018; 7; 1); )))))))&B3 <
     IF(Sheet1!$B$3=TEXT(DATE(2018; 1; 1); "dddd"); DATE(2018; 1; 1); 
     IF(Sheet1!$B$3=TEXT(DATE(2018; 2; 1); "dddd"); DATE(2018; 2; 1); 
     IF(Sheet1!$B$3=TEXT(DATE(2018; 3; 1); "dddd"); DATE(2018; 3; 1); 
     IF(Sheet1!$B$3=TEXT(DATE(2018; 4; 1); "dddd"); DATE(2018; 4; 1); 
     IF(Sheet1!$B$3=TEXT(DATE(2018; 5; 1); "dddd"); DATE(2018; 5; 1); 
     IF(Sheet1!$B$3=TEXT(DATE(2018; 6; 1); "dddd"); DATE(2018; 6; 1); 
     IF(Sheet1!$B$3=TEXT(DATE(2018; 7; 1); "dddd"); DATE(2018; 7; 1); )))))))&Sheet1!$B$4)

paste this formula to Sheet2!B4 and drag it over to Sheet!C4 cell

Related Topic