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.