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.

 

[BThis]

Peter, I really thank you for your help. When I run the macro on my spreadsheet, I get an error

When I click debug, the following statement is highlighted:

I noticed that you're creating an array of columns B:C, but the relevant columns in my spreadsheet are B and E. Column A is the client name. We record the date we receive a project (column B), the date it's approved (column C) (usually B & C are the same date). We capture the due date in column D, and the Transmit Date in Column E. Projects last from the Date Approved to the Transmit Date, so columns C and E define the length of each project. Does this make sense?

So, I saved a new version and deleted the Date Received (column B) and moved the Transmit Date to the left to put those columns together. So now Column B is Date Approved and Column C is Transmit date, assuming that would match your columns B:C.

But when I ran the macro again I got a different error: "Type Mismatch", and Clicking Debug highlighted
StartYr = Year(Application.Min(Columns("B:C")))

Obviously I'm way over my head. Got any Suggestions?

 

[Peter_SSs]

I noticed that you're creating an array of columns B:C, but the relevant columns in my spreadsheet are B and E.

Actually my code is creating an array of columns B & D because that is what you stated

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

However, I did have an error in that I forgot to amend how the first and last years were calculated & that is where you are seeing columns B:C mentioned. I will amend the code for that error when we resolve the columns issue.

As well as being unsure about the "end" column being D or E, I am also unsure about the heading of the column. In post #7 you said it was "Due Date". Now you are saying it is "Transmit Date". Which is it?

There also seems some ambiguity about the column to use for the start date. Can you confirm the heading of the column that should be used? "Date Received" or "Date Approved"?

It looks like your data is a formal Excel table. What is the name of the table? If unsure, go to the Formulas ribbon tab and look in the Name Manager.

 

[BThis]

Peter, I really thank you for your help. When I run the macro on my spreadsheet, I get an error View attachment 12204
When I click debug, the following statement is highlighted: View attachment 12203

I noticed that you're creating an array of columns B:C, but the relevant columns in my spreadsheet are B and E. Column A is the client name. We record the date we receive a project (column B), the date it's approved (column C) (usually B & C are the same date). We capture the due date in column D, and the Transmit Date in Column E. Projects last from the Date Approved to the Transmit Date, so columns C and E define the length of each project. Does this make sense?
View attachment 12205

So, I saved a new version and deleted the Date Received (column B) and moved the Transmit Date to the left to put those columns together. So now Column B is Date Approved and Column C is Transmit date, assuming that would match your columns B:C.

View attachment 12206

But when I ran the macro again I got a different error: "Type Mismatch", and Clicking Debug highlighted
StartYr = Year(Application.Min(Columns("B:C")))

Obviously I'm way over my head. Got any Suggestions

 

[Peter_SSs]

Got any Suggestions

I probably will have - when you address the questions I asked last time.

As well as being unsure about the "end" column being D or E, I am also unsure about the heading of the column. In post #7 you said it was "Due Date". Now you are saying it is "Transmit Date". Which is it?

There also seems some ambiguity about the column to use for the start date. Can you confirm the heading of the column that should be used? "Date Received" or "Date Approved"?

It looks like your data is a formal Excel table. What is the name of the table? If unsure, go to the Formulas ribbon tab and look in the Name Manager.

 

[BThis]

Sorry, somehow I missed your post with the questions.

As well as being unsure about the "end" column being D or E, I am also unsure about the heading of the column. In post #7 you said it was "Due Date". Now you are saying it is "Transmit Date". Which is it?

"Transmit Date" would be the most accurate. Some items did not have Transmit Dates entered, but I've looked them all up and completed the column.

There also seems some ambiguity about the column to use for the start date. Can you confirm the heading of the column that should be used? "Date Received" or "Date Approved"?

"Date Received"

It looks like your data is a formal Excel table. What is the name of the table? If unsure, go to the Formulas ribbon tab and look in the Name Manager.

The table is called "Completed"

 

[Peter_SSs]

Thanks for those additional answers. Try this

Sub MaxConcurrent_v3()
  Dim d As Object
  Dim a As Variant
  Dim i As Long, DRcol As Long, TDcol As Long
  Dim j As Date
  
  Set d = CreateObject("Scripting.Dictionary")
  With ActiveSheet.ListObjects("Completed")
    DRcol = .ListColumns("Date Received").Index
    TDcol = .ListColumns("Transmit Date").Index
    a = Application.Index(.DataBodyRange, Evaluate("row(1:" & .DataBodyRange.Rows.Count & ")"), Array(DRcol, TDcol))
  End With
  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

 

[BThis]

Thanks, Peter. I get the following error:
Run-time error '9'
Subscript out of range

 

[Peter_SSs]

On what line of code when you Debug?

 

[BThis]

With ActiveSheet.ListObjects("Completed")

 

[Peter_SSs]

With ActiveSheet.ListObjects("Completed")

That would indicate that the table is not on the active sheet or else it isn't called "Completed"