Problem with getting a count of event start and end date overlaps

darksidekilo6

New Member
First time poster but not for a lack of post surfing. I have a list of events with start and end dates for specific units. I am trying to determine if any of the events for a specific unit have start and end dates that overlap or fall between each other and then count the number that meet the below criteria:

X = event start date
y = event end date
a = all start dates
b = all end dates

1. x is between a and b, but y is >= b
2. x is <= a and y is > a but <= b
3. Both x and y are between a and b
4. x is >= to a, but y is >= b

I am using the following formula in column D to return "Overlap" if overlaps that meet the above criteria exist or return "Ok" if no overlaps meeting the above criteria exist.

Column A is the unit uniquew designator
Column B is all of the start dates
Column C is all of the end dates

Code:
``=IF(SUMPRODUCT(--(\$A2=\$A\$2:\$A\$12),--(\$C2>=\$B\$2:\$B\$12),--(\$B2<=\$C\$2:\$C\$12))=1,"OK","Overlap")``

I have used numerous formulas to determine if overlaps exist all of which seemed to have worked. The above is just the most recent formula that appears to work correctly returning the appropriate text.

Now in column E, I am trying to count the number of overlaps for a specific unit if Column D returns overlap. Below is the formula I am currently using that returns random results.

Code:
``=IF(\$D2="Overlap",SUMPRODUCT(--(\$A2=\$A\$2:\$A\$12),--(\$C2>=\$B\$2:\$B\$12),--(\$B2<=\$C\$2:\$C\$12)),0)``

Here are the results:

 Col A Col B Col C Col D Col E Col F Unit Start Date End Date Test Count Remarks A 5/22/2016 5/30/2016 Overlap 4 B 5/23/2016 5/25/2016 No A 5/23/2016 5/25/2016 Overlap 3 Col E should be 4 A 5/25/2016 5/28/2016 Overlap 4 B 5/27/2016 5/30/2016 No A 5/28/2016 5/30/2016 Overlap 3 Col E should be 4 A 7/5/2016 7/15/2016 No

<TBODY>
</TBODY>

Thanks in advance for any help on getting me back on track.

Sean

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Bump! Still looking for possible methods. Conducted some more research but I still can't get a consistent number of overlapping events for a unit. I've tried SUMPRODUCT, IF(OR, and IF(AND with no luck.

Replies
8
Views
240
Replies
1
Views
247
Replies
3
Views
248
Replies
5
Views
329
Replies
5
Views
395

1,203,125
Messages
6,053,655
Members
444,676
Latest member
locapoca

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back