find out overlap time

[Goodstart14]

Hi,
I'd like to find overlap time in time sheet.
I hope my table looks like below;

Date	Start Time	End Time	Overlap?
12/5/2016	11:00	15:00	Yes
12/5/2016	12:00	13:00	Yes
12/6/2016	11:00	13:00	No

How can I use formula for 12/6/2016?
Because I can know 'Overlap' for the same date, 12/5/2016, using =IF(SUMPRODUCT((A2<$B$2:$B$5)*(B2>$A$2:$A$5))>1,"Yes","No"), but I also get 'Yes' for 12/6/2016.
How can I adjust this Sumproduct formula to get 'No' on 12/6/2016?
Thanks.

[CAntosh]

Not sure if I'm 100% clear on what you're after, but I went with COUNTIFS. Specifically:

=IF(COUNTIFS($A$2:$A$8,$A2,$C$2:$C$8,">"&$B2,$B$2:$B$8,"<"&$C2)>1,"Yes","No")

...in D2, with Date | Start | End in A,B, and C. Check out the attachment and see if it does the trick.

[Goodstart14]

Thanks for your help.
It seems working except '0:00', midnight.
Instead '24:00", I use '0:00' in the drop down in Start Time and End Time.
When I play with 0:00, then it's not right.

[CAntosh]

Yeah, Excel considers 0:00 to be the start of a day. You could either use 23:59 for midnight instead, or you could replace the formula I proposed with one that should take into account the 0:00 entries:

=IF(COUNTIFS($A$2:$A$8,$A2,$C$2:$C$8,">"&$B2,$B$2:$B$8,"<"&$C2)+COUNTIFS($A$2:$A$8,$A2,$C$2:$C$8,TIME(0,0,0),$B$2:$B$8,"<"&$C2)+IF(AND($C2=TIME(0,0,0),COUNTIFS($A$2:$A$8,$A2,$C$2:$C$8,">"&$B2)>=1),2,0)>1,"Yes","No")

Try it out, see if it holds up...

[Goodstart14]

Thanks!!
It worked.
I want to put this formula in VBA, but I can't.
Specially below part;

IF(COUNTIFS($A$2:$A$8,$A2,$C$2:$C$8,">"&$B2

I added quote at ">", so it looks like "">"".
How about '&$B2' ? How can I write this in vba?

Thanks

[CAntosh]

Usually the surest way to figure out how to translate a formula to VBA is to record a macro of yourself entering the formula in a cell, then look at the syntax of the recorded macro. My effort returned this:

Please Login or Register  to view this content.

R1C1 isn't my favorite thing, but the recording takes the strain off of figuring out where all of the quotes need to go.

[Goodstart14]

Thanks for your help!