Time Overlaps and Identifying Gaps

**Angiebaby888** · 06-13-2019, 09:20 AM

Hello
I need a solution to the following:
I have lots of engineer report records with dates and times (in different columns) where I need to filter out both gaps (in the working day 08:30 - 17:00) and any overlapping times. Preferably I would need to home in on the records that overlap with the amount of time displayed that is overlapping.

For example:
Start Time End Time Overlap Time
08:00 09:00 No
08:55 10:35 Yes
10:00 12:50 Yes....
12:01 16:00
15:00 16:00
15:45 17:30

Much appreciated if someone could assist

Angelena

**GR00007** · 06-13-2019, 02:38 PM

This is assuming that the rows will be in sequence. Place formula in C2 and copy down.

Please Login or Register  to view this content.

Example:

Start	End	Overlap
8:00	8:45
8:55	10:35	-00:10
10:00	12:50	0:35
12:01	16:00	0:49
15:00	16:00	1:00
15:45	17:30	0:15

The formula has been modified - it would have been nice just to remove the check for less than zero and have a negative display for gaps in time (positives are overlap) - but Excel displays ##### for negative times by default, and it takes an advanced config change to allow them. So, if the time calc is negative, this formula manually adds a "-" in front of text function on the absolute time diff formatted.

**Angiebaby888** · 06-14-2019, 05:42 AM

Hello, many thanks for this.....you're a STAR!
The other issue I have is identifying gaps in time in the working day (between 08:30 - 17:00).
I am so short on time and cannot find a suitable fornula.
Any help with this would be so awesome
Thanks

**JeteMc** · 06-19-2019, 12:46 PM

Hello Angelena and Welcome to Excel Forum.
See if the following will work for identifying gaps within the work day: =IFERROR(IF(B1 -A2>=0,"",MIN(A2-B1,A2-"8:30","17:00"-B1)),"")
Let us know if you have any questions.