Finding Time overlaps with Multiple Criteria

Mandalorian

New Member
Joined
Jan 12, 2020
Messages
3
Office Version
365
Platform
Windows
I complete a daily and weekly time sheets for each of my 30 employees.

the time is uploaded systemically through our ticketing software by each employee throughout their day.

I run the report daily and the time is then sorted by Start Time - smallest to largest, Date - Oldest to Newest and Last Name - A to Z

it puts the report chronologically in order by Last name and the dates they work and then the time should be all in order. Save Overlaps, when one ticket was open and another was actioned without the first one being in a hard stop condition.

I wish to single out these overlaps and highlight them and bring a 1 if True and 0 if False (no overlaps).

I use Sumproduct and can retrieve if using one persons time without looking for the name and multiple dates

=SUMPRODUCT(--($B$2:$B$10=B2),(C2>$C$2:$C$10)*(C2<$D$2:$D$10)+(D2>$C$2:$C$10)*(D2<$D$2:$D$10))

this works great for just one person

DateStart TimeEnd Timeoverlaps
1/1/20209:0013:000
1/1/202013:0013:451
1/1/202013:4016:002
1/1/202015:4518:001
1/1/202018:1519:000
1/2/20209:1510:001
1/2/20209:4513:001
1/2/202013:0015:000
1/2/202015:0016:000

as I stated, i need to add by Last name into the formula. would an nested IF statement work for this?

so i add to the formula

=SUMPRODUCT(--($A$2:$A$10=A2),($B$2:$B$10=B2),(C2>$C$2:$C$10)*(C2<$D$2:$D$10)+(D2>$C$2:$C$10)*(D2<$D$2:$D$10))

and i get False or 0 all the way down

Last NameDateStart TimeEnd Timeoverlaps
Bost1/1/20209:0013:000
Bost1/1/202013:0013:450
Bost1/1/202013:4016:000
Bost1/1/202015:4518:000
Bost1/1/202018:1519:000
Hill1/2/20209:1510:000
Hill1/2/20209:4513:000
Hill1/2/202013:0015:000
Hill1/2/202015:0016:000

can anyone help with this?
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,219
Office Version
365
Platform
Windows
I think there should be a -- in front of the B part, a la --($B$2:$B$10=B2)
 

Watch MrExcel Video

Forum statistics

Threads
1,095,171
Messages
5,442,805
Members
405,198
Latest member
Florence Thomas

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top