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

Posted on 2011-10-24
Last Modified: 2012-05-12

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!

Question by:RobStl
    LVL 19

    Accepted Solution

    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

    Author Comment

    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.
    LVL 24

    Expert Comment

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

    Author Comment

    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.
    LVL 24

    Expert Comment

    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.

    Author Comment

    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!


    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now