Finding Time overlaps with Multiple Criteria

[Mandalorian]

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?

 

[DRSteele]

I think there should be a -- in front of the B part, a la --($B$2:$B$10=B2)

 

[Mandalorian]

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]

That did it - Thanks!