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


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":
 Overlap Example
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!

Who is Participating?
Arno KosterCommented:
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

Open in new window

currently my regional settings do not allow me to thoroughly check the code but it should work
RobStlAuthor 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.
Would you be expecting an answer of 3 or 4 for your example? I would have thought 4 (objects 1,3,4,6).
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

RobStlAuthor 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.
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


array formula so use Ctrl+shift+enter

Then somewhere else


will give you the answer. It worked for your example, but no guarantee it will work every time.
RobStlAuthor 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!

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.

All Courses

From novice to tech pro — start learning today.