# Excel VBA Algorithm: Determine Number of Objects that Overlap Given a Date Timeframe

Hey,

I have been trying to come up with an algorithm to determine how many objects will overlap / happen at the same time, given a date timeframe.  Each object has a lifetime of 10 days.  You are given a certain number of objects, and the dates that these objects start.  My goal of this algorithm is to determine how many objects could be happening at the same time.

For example, say you are given 6 objects and their start dates:
Object 1: 9/25/11
Object 2: 9/13/11
Object 3: 10/3/11
Object 4: 10/8/11
Object 5: 10/21/11
Object 6: 10/10/11

Here is a visual of these objects to help show the idea of what I mean by object "overlap":

So... visually, it is pretty easy to see that the maximum number of objects that "overlap" is 3 (Objects 3, 4, and 6).  There is also an overlap of 2 at the beginning of October with objects 1 and 3.  So my question is: How can I find the maximum overlap of X objects, given the start dates for each object?  Any help would be greatly appreciated!

Regards,
Rob
###### Who is Participating?

Commented:
can you try this :
``````Sub test()

'Object 1: 9/25/11
'Object 2: 9/13/11
'Object 3: 10/3/11
'Object 4: 10/8/11
'Object 5: 10/21/11
'Object 6: 10/10/11

objects = Array("9/25/11", "9/13/11", "10/3/11", "10/8/11", "10/21/11", "10/10/11")
Duration = 10
max_concurrent_items = 0

For check_date = CDate("9/1/11") To CDate("12/1/11")
concurrent_items = 0
For Each Item In objects
If check_date >= CDate(Item) And check_date < CDate(Item) + 10 Then concurrent_items = concurrent_items + 1
Next Item
Debug.Print "Total number of concurrent objects on " & check_date & " = " & concurrent_items
If concurrent_items > max_concurrent_items Then max_concurrent_items = concurrent_items
Next check_date

MsgBox "Maximum number of overlapping objects = " & max_concurrent_items

End Sub
``````

currently my regional settings do not allow me to thoroughly check the code but it should work
0

Author Commented:
Thank you for your comment, akoster.  I will check this out soon and get back to you once I have implemented this into my code.
0

Commented:
Would you be expecting an answer of 3 or 4 for your example? I would have thought 4 (objects 1,3,4,6).
0

Author Commented:
I would be expecting 3, which does make a difference.   I am simply using this number to determine the row height.  I am dynamically creating these shapes and need to know the "maximum overlap" at any one date.  So for my example, 3 would be the maximum number of overlaps from 10/10-10/13.
0

Commented:
OK, sorry I was being dense! I think I have a formula solution, but it's a bit long-winded (sure one of the guys here could sub it down)

If your start dates are in B1:B6, end dates (col B+10) in C1:C6, then this in D1:D6

=SUM(((\$B1<\$B\$1:\$B\$6)*(\$C1>\$B\$1:\$B\$6))+((\$B1<\$C\$1:\$C\$6)*(\$C1>\$C\$1:\$C\$6)))

array formula so use Ctrl+shift+enter

Then somewhere else

=MAX(D1:D6)

will give you the answer. It worked for your example, but no guarantee it will work every time.
0

Author Commented:
Thank you guys for your help.  I modified the code slightly to fit my existing date array, but the algorithm worked perfectly for what I need.

I also tried out your idea Stephen, but I needed the solution in VBA.  Either way, thanks again for your guys' help!

Rob
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.