How to Find Time Overlaps

J

JK_NMS

New Member
Joined
Dec 19, 2023
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hello!
Ok I am in need of some help here. I am reviewing timesheets and looking for any overlapping punched in time slots.

Example:
NameDateStart TimeEnd TimeOverlap
Bob12/11/202310:00 AM10:24 AMOverlap
Bob12/11/202310:00 AM10:17 AMOverlap
Bob12/11/202310:04 AM10:13 AMOverlap
Bob12/11/202310:18 AM10:21 AMOverlap
Bob12/11/202310:22 AM10:30 AM
Bob12/11/202310:32 AM10:35 AM
Bob12/11/202310:32 AM10:35 AMOverlap
Bob12/11/202310:33 AM10:36 AMOverlap
Bob12/11/202310:36 AM10:39 AMOverlap
Bob12/11/202310:50 AM10:57 AM
Bob12/11/202310:57 AM11:21 AM
Bob12/11/202311:23 AM11:26 AM
Jane12/8/202310:35 AM10:39 AM
Jane12/8/202310:47 AM10:48 AM
Garry12/8/202310:30 AM11:15 AMNo Overlap
Garry12/8/202311:15 AM12:15 PMNo Overlap
 

Attachments

  • Time Overlap.png
    Time Overlap.png
    26 KB · Views: 8

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
Share XL2BB of your actual data. The data you have shared is not sorted. Moreover it has many challenges that actual data might not have.
 
Upvote 0
Hello!
Per SanjayGMusafir request:
Office Version 365
Platform Windows
Microsoft® Excel® for Microsoft 365 MSO (Version 2311)

The times that are provided are manually entered in by employees. I need to perform a weekly audit to ensure that there are no overlaps. There are times where there may be multiple entries that overlap. Everything provided are examples that we consistently run into.
Assistance on streamlining this process would be greatly appreciated.

I am unable to upload the Excel document as my work computer is not allowing me to download the XL2BB file.

I adjusted sorting the times if this makes it easier to understand:

NameDateStart TimeEnd TimeOverlap
Bob12/11/202310:00 AM10:17 AMOverlap
Bob12/11/202310:00 AM10:24 AMOverlap
Bob12/11/202310:04 AM10:13 AMOverlap
Bob12/11/202310:18 AM10:21 AMOverlap
Bob12/11/202310:22 AM10:30 AM
Bob12/11/202310:32 AM10:35 AM
Bob12/11/202310:32 AM10:35 AMOverlap
Bob12/11/202310:33 AM10:36 AMOverlap
Bob12/11/202310:36 AM10:39 AMOverlap
Bob12/11/202310:50 AM10:57 AM
Bob12/11/202310:57 AM11:21 AM
Bob12/11/202311:23 AM11:26 AM
Garry12/8/202310:30 AM11:15 AMNo Overlap
Garry12/8/202311:15 AM12:15 PMNo Overlap
Jane12/8/202310:35 AM10:39 AM
Jane12/8/202310:47 AM10:48 AM


1703017804775.png
 
Upvote 0
My company computer restrictions do not allow me to install and utilize XL2BB.

Current Excel Version & Platform:
Office Version 365
Platform Windows
Microsoft® Excel® for Microsoft 365 MSO (Version 2311)
*already in profile
 
Upvote 0
Check this and revert -

please note as under -
  1. It is suggested to convert your data to a Formatted Table. It shall help you over a longer period
  2. The Data needs to be sorted in Order - Name, date, Start time, End time for formula to work properly.
  3. Your sample data has few wrong overlaps marked that my formula has eliminated. Please check for the accuracy of formula for that purpose.
Book1
ABCDEF
1NameDateStart TimeEnd TimeOverlapOverlap Check
2Bob11/12/2310:00 AM10:17 AMOverlap
3Bob11/12/2310:00 AM10:24 AMOverlapOverlap
4Bob11/12/2310:04 AM10:13 AMOverlapOverlap
5Bob11/12/2310:18 AM10:21 AMOverlap 
6Bob11/12/2310:22 AM10:30 AM 
7Bob11/12/2310:32 AM10:35 AM 
8Bob11/12/2310:32 AM10:35 AMOverlapDuplicate
9Bob11/12/2310:33 AM10:36 AMOverlapOverlap
10Bob11/12/2310:36 AM10:39 AMOverlap 
11Bob11/12/2310:50 AM10:57 AM 
12Bob11/12/2310:57 AM11:21 AM 
13Bob11/12/2311:23 AM11:26 AM 
14Garry8/12/2310:30 AM11:15 AMNo Overlap 
15Garry8/12/2311:15 AM12:15 PMNo Overlap 
16Jane8/12/2310:35 AM10:39 AM 
17Jane8/12/2310:47 AM10:48 AM 
Sheet1
Cell Formulas
RangeFormula
F3:F17F3=IFS(AND(A3=A2,(B3+C3/24)=(B2+C2/24),(B3+D3/24)=(B2+D2/24)),"Duplicate",AND(A3=A2,(B3+C3/24)<=(B2+D2/24),(B3+C3/24)<(B2+D2/24)),"Overlap",TRUE,"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E2:E17Expression=AND(E2="Overlap",E2<>F2)textNO
 
Upvote 0
This does not work with what I am trying to achieve

I have marked what is considered an overlap in time and what is not. I am looking to utilize a formula that will inform me if there are multiple overlaps in time punches and inform me if there is an overlap.

NameDateStart TimeEnd TimeOverlap
Bob12/11/202310:00 AM10:17 AMOverlap
Bob12/11/202310:00 AM10:24 AMOverlap
Bob12/11/202310:04 AM10:13 AMOverlap
Bob12/11/202310:18 AM10:21 AMOverlap
Bob12/11/202310:22 AM10:30 AMNo
Bob12/11/202310:32 AM10:35 AMNo
Bob12/11/202310:32 AM10:35 AMOverlap
Bob12/11/202310:33 AM10:36 AMOverlap
Bob12/11/202310:36 AM10:39 AMOverlap
Bob12/11/202310:50 AM10:57 AMNo
Bob12/11/202310:57 AM11:21 AMNo
Bob12/11/202311:23 AM11:26 AMNo
Garry12/8/202310:30 AM11:15 AMNo
Garry12/8/202311:15 AM12:15 PMNo
Jane12/8/202310:35 AM10:39 AMNo
Jane12/8/202310:47 AM10:48 AMNo


1703020440098.png
 
Upvote 0
JK_NMS said:
I have marked what is considered an overlap in time and what is not. I am looking to utilize a formula that will inform me if there are multiple overlaps in time punches and inform me if there is an overlap.
Click to expand...
Check this and revert -
Just change formula as below -

Excel Formula: 
=IFS(AND(A3=A2,(B3+C3/24)=(B2+C2/24),(B3+D3/24)=(B2+D2/24)),"Overlap",AND(A3=A2,(B3+C3/24)<=(B2+D2/24),(B3+C3/24)<(B2+D2/24)),"Overlap",TRUE,"No")
 
Upvote 0
Unfortunately the provided formula does not work for what I am trying to achieve

Time Slot (Start Time to End Time)
#1 Time Slot - Overlaps #2 & #3
#4 Time Slot - Overlaps #2

Time Slot entries such as #1-#4 are common and will need to be found

1703021337081.png
 
Upvote 0
Unless you share your actual data using XL2BB, I can't help any further. I have no way to find where and how things are going wrong.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
sumifs using date and time values
Replies
9
Views
368
Asbestos_Jen
Asbestos_Jen
Kellie220
  • Question Question
VBA to Collapse Rows (3 part question)
Replies
1
Views
283
Artik
Artik
S
  • Question Question
Formula to identify the time & frequency of max value.
Replies
5
Views
283
Cubist
Cubist
O
  • Solved
Macro to extract repeating invoice numbers and details
Replies
4
Views
245
Omar M
O
F
  • Question Question
VBA Macro Issue: Replacing Simple Formula with Complex Formula Causes Error
Replies
2
Views
124
flashgordie
F

Forum statistics

Threads
1,224,823
Messages
6,181,170
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top