*** HELP *** Checking for time overlap of multiple records based on criteria

[mpappis]

Hello,

This may be one of those "impossibles", but I am stuck on where to even start with this formula.

I am trying to use EXCEL to create a small resource schedule. Each "row' can be considered an activity or a shift. If for a particular "resource name" the resource has been double booked (same day, same overlap) i would like to see a "conflict" or "warning" as the output.

columns:
resource name, Start Time, End Time, Conflict?
frank, 6/14/2010 8:00 AM, 6/14/2010 2:00 PM, No
dan, 6/14/2010 10:00 AM, 6/14/2010 5:00 PM, No
Dan, 6/15/2010 11:00 AM, 6/15/2010 3:00 PM, No
Shelly, 6/15/2010 8:00 AM, 6/14/2010 1:00 PM, No

New rows gets created as:
frank, 6/14/2010 1:30 PM, 6/14/2010 4:00 PM, Yes
frank, 6/14/2010 5:00 AM, 6/14/2010 8:15 AM, Yes
frank, 6/14/2010 12:00 PM, 6/14/2010 1:00 PM, Yes

additionally, the very first row would change "yes"..

so what formula do I put in each row for "conflict" to get the appropriate response.

sincerely....

 

[shemayisroel]

Yes. It worked for me.

Make sure the date-times in columns B and C are actual date-time values that excel recognizes and not just text that may just "look" like a date-time.

My apologies - yes the logic does work. I was working on a sheet with auto calc off -dir

 

[AlphaFrog]

=IF(ISBLANK(A2),"",IF(SUMPRODUCT(($A$2:$A$100=A2)[COLOR="Red"]*[/COLOR](($B$2:$B$100<=B2)[COLOR="Red"]*[/COLOR]($C$2:$C$100>=B2)[COLOR="Red"]+[/COLOR]($B$2:$B$100<=C2)[COLOR="Red"]*[/COLOR]($C$2:$C$100>=C2)))>2,"Yes","No"))

In the Sumproduct portion of the formula, each part that's in parenthesis e.g ($C$2:$C$100>=B2) is a test for each row. It will return a 1 for True or 0 for false if a given row passes or fails the test.

Eech * in between each test will multiply two tests for a given row. This is the same as an AND for the two tests. Each + is equivalent to an OR.

So it tests...
1.) if a given Start-date-time is in between any other Start-date-time AND End-date-time.

OR
2.) if a given End-date-time is in between any other Start-date-time AND End-date-time.

If it matches either of those two, that indicates there is an overlap and will result in a "Yes"

One situation where there could be a problem is if say the first Frank event was a really long event covering multiple days. There are other Frank events that fall within the 1st Frank event. The other Frank events will be tagged as Yes and that's good. But if the 1st Frank event doesn't have a Start-date-time or End-date-time that falls within any other Frank event, it won't be tagged as Yes. The formula is really checking the Start and End and not the actual overlap.

Also, here is a tweaked formula. I realized if two evens didn't overlap but were just adjacent e.g one event Ended at 11:00 AM and another Started at 11:00 AM on the same day, you would get a false "Yes". The formula below fixes that.

=IF(ISBLANK(A2),"",IF(SUMPRODUCT(($A$2:$A$100=A2)*(($B$2:$B$100<=B2)*($C$2:$C$100 >B2)+($B$2:$B$100< C2)*($C$2:$C$100>=C2))) >2,"Yes","No"))

 

[mpappis]

Hi Alpha, Thank you for the updated formula and the explanation.

I actually figured the whole thing out last night. Once i figured out that sumproduct resolves conditional statements to 1 and 0s, the rest was history

i'll be using your tweaked formula, but I actually need to add in a 1 hour buffer between each shift, because people need to travel.....

thank you so much for answering this post.
best regards.

 

[mpappis]

It appears the formula actually had a small bug where not all records were identified as conflicts in the scenario where only 2 records were in conflict with each, and 1 of the records time (start and end) was completely inside the time record. This wasn't caught in the data example, because 4 records had errors of different conditions.
record 1 - 11 am to 1 pm
record 2 - 12pm to 12:30 pm
only record 2 was identified as conflict, and not both record 1 and 2. my updated formula fixes this problem

=IF(ISBLANK(A2),"",IF(SUMPRODUCT(($A$2:$A$100=A2)*(($B$2:$B$100<=B2)*($C$2:$C$100>B2)+($B$2:$B$100<C2)*($C$2:$C$100>=C2)+($B$2:$B$100>=B2)*($C$2:$C$100<=C2)))>3,"Yes","No"))<c2)*($c$2:$c$100>

<c2)*($c$2:$c$100><c2)*($c$2:$c$100><c2)+($b$2:$b$100></c2)+($b$2:$b$100></c2)*($c$2:$c$100></c2)*($c$2:$c$100></c2)*($c$2:$c$100>

 

[mpappis]

my posting is possessed, i tried to post my formula like 7 times, but every time it changes 1 spot from $B$2:$B$100<c2)*($c$2:$c$100><C2 to </c2)*($c$2:$c$100> $B$2:$B$100<c2)*($c$2:$c$100>=C2. has anyone else experienced this demonish behavior before????? i give up... anyway, my updated formula with a "<" in the one spot indicated, is working fine...

</c2)*($c$2:$c$100>