How many concurrent projects, by start date and end date?

[BThis]

I need to calculate the number of concurrent projects my team has managed per day 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
ProjectA 1/6/2020 1/24/2020
ProjectB 1/12-2020 1/22/2020
ProjectC 1/19/2020 2/3/2020
ProjectD 1/26/2020 2/9/2020
ProjectE 2/2/2020 2/7/2020

The first three projects overlap, so we were working on 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.

 

[GraH]

Like this? But what if projects are not ended yet?

Book1
	A	B	C	D	E	F	G	H
1	Description	Start	End				Date	Count
2	ProjectA	6/01/2020	24/01/2020				1/01/2020	0
3	ProjectB	12/01/2020	22/01/2020				2/01/2020	0
4	ProjectC	19/01/2020	3/02/2020				3/01/2020	0
5	ProjectD	26/01/2020	9/02/2020				4/01/2020	0
6	ProjectE	2/02/2020	7/02/2020				5/01/2020	0
7							6/01/2020	1
8							7/01/2020	1
9							8/01/2020	1
10							9/01/2020	1
11							10/01/2020	1
12							11/01/2020	1
13							12/01/2020	2
14							13/01/2020	2
15							14/01/2020	2
16							15/01/2020	2
17							16/01/2020	2
18							17/01/2020	2
19							18/01/2020	2
20							19/01/2020	3
21							20/01/2020	3
22							21/01/2020	3
23							22/01/2020	3
24							23/01/2020	2
25							24/01/2020	2
26							25/01/2020	1
27							26/01/2020	2
28							27/01/2020	2
29							28/01/2020	2
30							29/01/2020	2
31							30/01/2020	2
32							31/01/2020	2
33							1/02/2020	2
34							2/02/2020	3
35							3/02/2020	3
36							4/02/2020	2
37							5/02/2020	2
38							6/02/2020	2
39							7/02/2020	2
40							8/02/2020	1
41							9/02/2020	1
Sheet2

Cell Formulas
Range	Formula
H2:H41	H2	=COUNTIFS($B$2:$B$6,"<="&G2,$C$2:$C$6,">="&G2)

 

[Peter_SSs]

really I need the maximum number of concurrent projects each year.

If this is your goal, see if this helps. It assumes that the raw data is on the active sheet when the code is run and creates a new sheet for the results (assumes you do not already have a sheet called 'Max Per Year')
I have assumed that all projects in the list have an end date entered.

Give it a try with a copy of your data.

Sub MaxConcurrent()
  Dim d As Object
  Dim a As Variant
  Dim i As Long, StartYr As Long, EndYr As Long
  Dim j As Date
 
  Set d = CreateObject("Scripting.Dictionary")
  StartYr = Year(Application.Min(Columns("B:C")))
  EndYr = Year(Application.Max(Columns("B:C")))
  a = Range("A2", Range("C" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    For j = a(i, 2) To a(i, 3)
      If d.exists(Format(j, "yyyy yyyy-mm-dd")) Then
        d(Format(j, "yyyy yyyy-mm-dd")) = Format(j, "yyyy yyyy-mm-dd ") & Split(d(Format(j, "yyyy yyyy-mm-dd")))(2) + 1
      Else
        d(Format(j, "yyyy yyyy-mm-dd")) = Format(j, "yyyy yyyy-mm-dd ") & 1
      End If
    Next j
  Next i
  Application.ScreenUpdating = False
  Sheets.Add(After:=ActiveSheet).Name = "Max Per Year"
  With Sheets("Max per Year").Columns("A").Resize(d.Count + 1)
    .Cells(1).Value = "Year Date Count"
    .Cells(2).Resize(d.Count) = Application.Transpose(d.items)
    .Sort Key1:=.Cells(2), Order1:=xlAscending, Header:=xlYes
    .TextToColumns DataType:=xlDelimited, Space:=True, Other:=False
    .Resize(, 3).Subtotal GroupBy:=1, Function:=xlMax, TotalList:=Array(3)
    .Resize(, 3).Columns.AutoFit
    ActiveSheet.Outline.ShowLevels RowLevels:=2
  End With
  Application.ScreenUpdating = True
End Sub

Here is my sample data. My dates are in d/m/y format and I have added a couple of earlier projects so that we span more than one year.

BThis 2020-04-18 1.xlsm
	A	B	C
1	Description	Start	End
2	ProjectA	6/01/2020	24/01/2020
3	ProjectB	12/01/2020	22/01/2020
4	ProjectC	19/01/2020	3/02/2020
5	ProjectD	26/01/2020	9/02/2020
6	ProjectE	2/02/2020	7/02/2020
7	ProjectF	28/12/2019	5/01/2020
8	ProjectG	27/12/2019	29/12/2019
9
Sheet1

And my results. This shows the summary of maximum concurrent projects by year as you asked (I think )

If you want to see the data for all dates that had at least 1 project, click the little 3 at the top left to expand the SubTotals info.
You could also use columns B:C to graph the daily projects as you mentioned. Again, only days with projects are shown.

 

[BThis]

WOW! This is fantastic. *GraH, that's what I need, but it would be nice to graph it so I could see the peaks and know what the max is, or just calculate the maximum volume period.

Peter_SSs, that's brilliant! I will create a copy and run this on my data as soon as I get a break (and figure out again how to run VBA as I seldom have the opportunity to do so). I'm lucky to be still working, for an ecommerce company, and we're busier than ever during this lockdown. We are looking at new software that charges by how many concurrent projects we run, so we need to know!

THANK YOU BOTH! I'll report back this week on results, and proudly present it to my team.
--Barry

 

[GraH]

WOW! This is fantastic. *GraH, that's what I need, but it would be nice to graph it so I could see the peaks and know what the max is, or just calculate the maximum volume period.

Hi Barry, select that range and press ALT+F1. With a bit of luck your default chart is a column chart.

 

[Peter_SSs]

I will create a copy and run this on my data as soon as I get a break (and figure out again how to run VBA as I seldom have the opportunity to do so).

1. With your workbook active press Alt+F11 to bring up the vba window.
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Press Alt+F8 to bring up the Macro dialog
6. Select the macro & click ‘Run’
7. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Also, if you want the table (& graph) to show all dates, including those that have zero projects then my code could be amended to do that. Post back if you want that change.

 

[BThis]

Peter,

I hope you can help. The sample data I provided wasn't exactly how my spreadsheet is formatted. You're using "B:C" for the date columns. In my spreadsheet, the "Start Date" is column B, with heading "Date Received" and the "End Date" is Column D headed "Due Date." How do I edit the VBA to match those columns?

 

[Peter_SSs]

How do I edit the VBA to match those columns?

What version of Excel are you using? Please update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you are using Excel 365, do you have the UNIQUE() function?

 

[BThis]

Thank you, Peter, I clicked "2016" for the Office version. My Work laptop is a Windows 10 ThinkPad, running Microsoft Office Professional Plus 2016. I don't have the "UNIQUE()" function.

 

[Peter_SSs]

Thanks for updating your profile. Try this version

Sub MaxConcurrent_v2()
  Dim d As Object
  Dim a As Variant
  Dim i As Long, StartYr As Long, EndYr As Long, lr As Long
  Dim j As Date
  
  Set d = CreateObject("Scripting.Dictionary")
  StartYr = Year(Application.Min(Columns("B:C")))
  EndYr = Year(Application.Max(Columns("B:C")))
  lr = Range("B" & Rows.Count).End(xlUp).Row
  a = Application.Index(Cells, Evaluate("row(2:" & lr & ")"), Array(2, 4))
  For i = 1 To UBound(a)
    For j = a(i, 1) To a(i, 2)
      If d.exists(Format(j, "yyyy yyyy-mm-dd")) Then
        d(Format(j, "yyyy yyyy-mm-dd")) = Format(j, "yyyy yyyy-mm-dd ") & Split(d(Format(j, "yyyy yyyy-mm-dd")))(2) + 1
      Else
        d(Format(j, "yyyy yyyy-mm-dd")) = Format(j, "yyyy yyyy-mm-dd ") & 1
      End If
    Next j
  Next i
  Application.ScreenUpdating = False
  Sheets.Add(After:=ActiveSheet).Name = "Max Per Year"
  With Sheets("Max per Year").Columns("A").Resize(d.Count + 1)
    .Cells(1).Value = "Year Date Count"
    .Cells(2).Resize(d.Count) = Application.Transpose(d.items)
    .Sort Key1:=.Cells(2), Order1:=xlAscending, Header:=xlYes
    .TextToColumns DataType:=xlDelimited, Space:=True, Other:=False
    .Resize(, 3).Subtotal GroupBy:=1, Function:=xlMax, TotalList:=Array(3)
    .Resize(, 3).Columns.AutoFit
    ActiveSheet.Outline.ShowLevels RowLevels:=2
  End With
  Application.ScreenUpdating = True
End Sub