Overlapping tasks periods - Dates

  • 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.excelforum.com/exc…apping-tasks-periods.html


    PMC_Original - ENG.xlsm

  • Hello,


    Could you explain how the various tasks are identified ...


    What is the relationship between Tasks # and Nº of Tasks ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello Carim


    There is no relationship between the different tasks.

    They can be names, numbers, etc., without any relation.


    There is no relationship between Task # and Nº of Tasks. It was just a way of presenting the data.


    Thanks

  • Very sorry ... but read again your initial message several times ...


    and, to me, at least, it is totally ... incompreensível ...


    May be with an illustration of one single instance of what you are calling Overlapping between :


    1) the reference Start Date and End Date

    and

    2) a specific task and its respective Start Date and End Date

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thanks for your sample file


    What is better than one example ...?


    Two examples ...:)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Once you have tested the UDF ... feel free to share your comments ...


    By the way, did you get an answer in your CROSS POST ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi Carim

    The answer to your question is no, I did not receive an answer on my identified cross post!


    As for your solution, I would say that it almost answers what I need.


    It just doesn't answer because what I need, as I say in the post, is that the final result should be prioritized not by the number of overlapping tasks, but by the calculation that I refer to and then the sort will have to be sorted by the largest number as shown in the column "N ".


    It turns out that in the end I just need to show between the first three to five results of the calculation, so my question is whether this is possible to do and how, with arrays, because that way I stayed with all the calculations but only output the data to, the spreadsheets, with the values I want?


    Thank you very much for your help

  • Thanks for your Thanks ...AND for your Like :thumbup:


    Not sure to understand what is preventing you from adding the Weight column and formula ... as well as a ranking formula


    see attached Version 3


    Hope this will help

    :)

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • You are welcome.


    Should you face a difficulty for your macro ... do not hesitate to come back to the Forum

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi again,


    In you real-life workbook:


    1. What is the actual structure of your Data .... ???


    2. How many final results do your need ...??? ("show between the first three to five results")


    Ideally, posting a sample of the expected final result would help ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi again Carim


    This is the layout of my real-life workbook, I just changed the names of the tasks. As you can see, this is a Gantt layout in which I have several tasks and the Start (Column C) and End (Column E) Dates for each task.

    The data in this layout is not fixed, I repeat is not fixed and depends on the Department (Column L), that is, when I choose the department all values from Column A to Column K change., None of this is happening now, because I simplified this file as much as possible.

    The main columns for the case in question - task overlap periods - are columns N, O and P.

    Thus, 1, 2, 3 means:

    1 - first period with the highest concentration of tasks

    2 - second period of greatest concentration of tasks;

    3 - third period of greatest concentration of tasks.


    If task1 is in period 1 then column N shows a "1" if neither period 2 nor 3 appear in columns O and P then "0" appears - this has to do with conditional formatting.

    It is enough that one day of the task1 duration period, falls within any period 1, 2 or 3 to appear "1" in column N, O or P, that is, it is not necessary that the start and end date of a task is all contained in either period 1,2 or 3, one day is enough.

    This was what I needed .... not easy, I know!


    The cherry on top of the cake, was if it were possible, instead of having "1" and "0" in columns N, O and P, it was having the number of days for the duration of each task, which are in each period 1 , 2 and 3.


    I don't know if this explanation of mine is understandable - "compreensível", if you have any doubts tell me.


    Thank you so much again.

    Jorge

  • Hi Carim


    Since is not possible edit my last post, here is an update.


    I was seeing your solution better (initial file - TestPMC_Original-V3.xlsm), and I notice that it is not calculating the periods with the highest concentration of tasks, but only the number of tasks.


    Note that the available data is only the start and end date of the tasks. What was in the OUTPUT tab was all calculated manually.


    The data are in the DATA tab, columns A, B and C, what I want is the calculations that are in OUTPUT tab, columns A to F.