# Examine for overlap in Google Sheets?

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

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:

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
``````

## 1 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 • Phew!! I've got to slowly digest this one! That's quite the function.. – theforestecologist Sep 5 '18 at 1:55