lundi 6 avril 2020

How to calculate the overlap between some Google Sheet time frames?

I’m working on a Google sheet document where I have many sheets, one for each day.
In each sheet, I have the list of my tasks of the day, the time when it starts and the time when it ends.

I need to write a formula that calculates if there is an overlap between these time frames and show TRUE in case this is happening.
I would love to calculate everything in the cell of the overlap column, without adding any extra column.

I spent quite some time figuring out how to do it but I feel I don’t have enough competences to do it.

This is an example of the table format.

|-------------------|------------|-----------|-----------|
| Task Description  |  Started   |   Ended   |  Overlap  |
|-------------------|------------|-----------|-----------|
|  Task 1           |   07:40    |  09:10    |           |
|-------------------|------------|-----------|-----------|
|  Task 2           |   09:10    |  10:10    |  TRUE     |
|-------------------|------------|-----------|-----------|
|  Task 3           |   10:00    |  13:10    |  TRUE     |
|-------------------|------------|-----------|-----------|
|  Task 4           |   13:10    |  14:10    |           |
|-------------------|------------|-----------|-----------|
|  Task 5           |   14:10    |  15:40    |           |
|-------------------|------------|-----------|-----------|

Any suggestions will be appreciated.

Aucun commentaire:

Enregistrer un commentaire