Overlapping tasks periods

I come again to ask for help for the following problem:

- I have a set of tasks that have a start date for the task and an end date for the task;

- For this set of tasks I needed to know, by VBA, in an orderly way, which periods (date range) where there are more overlapping tasks;

- In a first stage, the ordering of the data will be made, for the largest number of overlaps and within those of the one with the greatest overlapping days, that is, for periods with the same number of overlaps, the first is what has more days.

I say that it is in a first phase since in fact the ordering will be done by calculating a new "WEIGHT" that will be equal to WEIGHT = A1 * D1 * A1 / 3, and the ordering will be from the highest to the lowest WEIGHT, as you can see in the data on the right in the “OUTPUT” tab.

In the file I attach, I have a set of data, tasks and dates, and the expected results, number of overlapping tasks, date range and number of days.

All help will be welcome
Jorge Cabral

PS: Also in https://www.ozgrid.com/forum/index.p...periods-dates/

Any help will be very welcome.

Thanks
Jorge Cabral

282 views and no bites suggests that either nobody understands what you are trying to do, or that what you are asking can't be done, or that nobody wants to help.

You might try, instead of just bumping the thread, adding some extra detail.

Not sure that I understand but will make a guess that you would like the data in columns L:O put into an order as shown in columns A:D.
Note that this is a formula based proposal, so if you are only looking for a VBA solution you may stop reading.
As modeled in columns R:U
The number of tasks column is populated using: =LARGE(L$2:L$74,ROWS(L$2:L2))
The other columns are populated using: =INDEX(M$2:M$74,AGGREGATE(15,6,(ROW($L$2:$L$74)-ROW($L$1))/($L$2:$L$74=$R2),COUNTIFS($R$2:$R2,$R2)))
Let us know if you have any questions.

I happened to be working more on this and think that I have what you want.
To get the overlaps use: =SUM(COUNTIFS(B3:B$47,"<="&C2,C3:C$47,">="&B2),COUNTIFS(B$1:B1,"<="&C2,C$1:C1,">="&B2))
To get the overlaps and dates in descending order:
1. Overlaps: =LARGE(D$2:D$46,ROWS(D$2:D2))
2. Dates: =INDEX(B$2:B$46,AGGREGATE(15,6,(ROW(B$2:B$46)-ROW(B$1))/($D$2:$D$46=$H2),COUNTIFS($H$2:$H2,$H2)))
To get the number of days: =J2-I2+1
Let us know if you have any questions.

Try pasting the following into cell L2 and then double clicking the fill handle to copy down:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

I thought that the formula was supposed to be used with the dates in column M on the Output sheet, before the range was sorted using the formulas in post #7.
In order to match the numbers that have been manually placed in column A modify the formula to read:

Formula:

Please Login or Register  to view this content.

Once entered into cell A2 on the Output sheet double click the fill handle to copy the formula down to A74.
Let us know if you have any questions.

Pl see file.
In F2

Please Login or Register  to view this content.

In G2 then copied to H2

Please Login or Register  to view this content.

In I2

=H2-G2

Copy down all.

On the Data sheet the colored areas of the Gantt chart seem to correspond to the dates in columns B and C.
There are numbers that correspond to the dates 10/22/2020 through 11/21/2020 which I assume are from the output sheet.
There are 16 tasks that include those dates. The formulas that I provided for columns A (post #13) and L (post #11) on the Output sheet both yield 16, furthermore the formulas yield all of the values that are manually placed in those columns.
Since neither of them seem to be what you want, I am confused as to what you want.

On the Data sheet the colored areas of the Gantt chart seem to correspond to the dates in columns B and C. – CORRECT

There are numbers that correspond to the dates 10/22/2020 through 11/21/2020 which I assume are from the output sheet. - These numbers were to show you that between the dates of 10/22/2020 and 11/21/2020, there are 16 overlapping tasks. It was just an example. What I want to know is precisely all these intervals and the number of overlapping tasks. What is in OUTPUT was calculated manually based on the GANTT diagram, Columns A to C of the OUTPUT tab, I repeat, I made the gantt diagram as it is on DATA and then I went to see each period and the number of tasks and manually filled in the columns from A to C to show what I intend to be done by VBA.

There are 16 tasks that include those dates. The formulas that I provided for columns A (post #13) and L (post #11) on the Output sheet both yield 16, furthermore the formulas yield all of the values that are manually placed in those columns. - It's not what I want, what I want are the overlap intervals and the number of overlapping tasks, columns A to C of the OUTPUT tab

Since neither of them seem to be what you want, I am confused as to what you want. - I hope to have clarifies above


Thank you so much
Jorge


Add new example explanation

Note: I was working on this when the file was added to post #17, so this proposal is based on the description in post #17 and the file attached to post #1.
This is a formula based proposal. I do not know enough about VBA to say whether this could be accomplished using VBA.
On the Data sheet E1:BRK1 are populated using: =IF(AND(E1<>"",SUM(E1,1)<=$C2),SUM(E1,1),"")
E2:BRK46 are populated using: =IF(AND($B2<=E$1,$C2>=E$1),1,0)
E48:BRK48 are populated using: =SUM(E2:E47)
E49:BRK49 are populated using: =IF(AND(D48<>"",E48<>D48),E1,"")
E50:BRK50 are populated using: =IF(AND(F48<>"",F48<>0,E48<>1,E48<>F48),E1,"")
The results are shown in columns R:T on the Output sheet.
Column R (no. of overlaps) is populated using: =IF(S2="","",INDEX(DATA!E$48:BRK$48,MATCH(S2,DATA!E$1:BRK$1,0)))
Column S (start date) is populated using: =IF(T2="","",AGGREGATE(15,6,DATA!E$49:BRK$49,ROWS(S$2:S2)))
Column T (end date) is populated using: =IFERROR(AGGREGATE(15,6,DATA!E$50:BRK$50,ROWS(S$2:S2)),"")
I want to be sure that this is acceptable before proceeding to sorting the results.
Let us know if you have any questions.