Concurrent projects by start and end dates?

  • I need to calculate the number of concurrent projects my team has managed using start and end dates.

    I have almost ten years of projects--about 600 total--in a spreadsheet with columns for start and end dates for each project. I have them all in a single sheet, plus I copied each year's projects (by end date) to separate tabs for reporting purposes. Each project may last from a few days to occasionally two months, with most projects lasting around two weeks. If it's better to use the annual tabs, I can afford to ignore projects that run across the new year because that's never our maximum busy period in terms of concurrent projects. The relevant data looks like this:

    Description Start Date End Date
    Project A 1/6/2020 1/24/2020
    Project B 1/12-2020 1/22/2020
    Project C 1/19/2020 2/3/2020
    Project D 1/26/2020 2/9/2020
    Project E 2/2/2020 2/7/2020

    The first three projects overlap, so there are three concurrent projects from 1/19 to1/22. I just need the number of concurrent projects, and really I need the maximum number of concurrent projects each year. Is there a way to graph it on a daily basis and see the peak days? Can anyone help?

    Thanks in advance.

  • There is a simple solution with Power Query. I do not know which Office Version you are using.

    Power Query is available for Office 2010 and 2013 as free downloadable add on from Microsoft and is included in newer versions.

    I added a Worksheet Change event function, so whenever something is changed in the Overview dataset there is an update of the Pivot Table and Chart.

    Your data must be in a table and the table must be named "tblProjectOverviewPQ". Otherwise you have to adopt the Power Query function and the worksheet event. see attachment ProjectsTimeSchedulePQ.xlsm

    hope this helps