# Finding Time overlaps with Multiple Criteria

#### Mandalorian

##### New Member
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

 Date Start Time End Time overlaps 1/1/2020 9:00 13:00 0 1/1/2020 13:00 13:45 1 1/1/2020 13:40 16:00 2 1/1/2020 15:45 18:00 1 1/1/2020 18:15 19:00 0 1/2/2020 9:15 10:00 1 1/2/2020 9:45 13:00 1 1/2/2020 13:00 15:00 0 1/2/2020 15:00 16:00 0

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 Name Date Start Time End Time overlaps Bost 1/1/2020 9:00 13:00 0 Bost 1/1/2020 13:00 13:45 0 Bost 1/1/2020 13:40 16:00 0 Bost 1/1/2020 15:45 18:00 0 Bost 1/1/2020 18:15 19:00 0 Hill 1/2/2020 9:15 10:00 0 Hill 1/2/2020 9:45 13:00 0 Hill 1/2/2020 13:00 15:00 0 Hill 1/2/2020 15:00 16:00 0

can anyone help with this?

### 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
I think there should be a -- in front of the B part, a la --(\$B\$2:\$B\$10=B2)

#### Mandalorian

##### New Member
I think there should be a -- in front of the B part, a la --(\$B\$2:\$B\$10=B2)
Thanks for your reply -I will try it and post the results.

#### Mandalorian

##### New Member
That did it - Thanks!

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