Overlapping tasks periods - Dates

  • Hi Jorge,


    Thanks for your file.


    Seems to me some key elements are missing ...


    In your sample file you had :


    1. Tasks with Start and End Dates in Sheet Data


    2. Projects with Start and End Dates in Sheet Output


    It is only with the Projects Start and End Dates which serve as References that you are in a position to compare them to the Tasks Start and End Dates ....and, afterwards only ... calculate the periods with the highest concentration of Tasks ...


    Thanks for your clarification

    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


    First of all I apologize for my English, I may not be completely clear on what I want.


    But it is like that, for each task I only have a start date and an end date for the task. There are no more data.

    Everything in the "OUTPUT" tab was manually calculated to show what I wanted, nothing of what is there is data, it is just the results of calculations.


    In tasks I have start and end dates for tasks, and what happens is that some of these dates overlap and it is these periods that we want to know.


    I hope I have been clearer now.


    Thank you so much again

  • Hello,


    From my point of view, have this strange feeling we are back to the start point ...:/


    Mainly because, in your real-life worksheet .. there is not a single indication of the final result which is expected ...

    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

    Let me clarify the following:


    In columns N, O and P are the results of the tasks being, or not, in the first three rank.


    The Periods, corresponding to 1, 2 and 3 rank will be represented graphically in lines 11, 12 and 13, by conditional formatting or by shapes I have not yet decided.

  • Hi,


    Thanks for your clarification ...


    To move forward ... The Periods, corresponding to 1, 2 and 3 rank ...must be Explicit ...!!!

    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


    I'm sorry, but I didn't understand what you mean by "To move forward ... The Periods, corresponding to 1, 2 and 3 rank ... must be Explicit ... !!!"


    After calculating the various periods and ranking them according to the criteria I presented, it is necessary to check if each task is within the 1 or 2 or 3 rank and when placing in the N, O or P column a value other than zero, an "X" is shown or not indicating whether the task is within the period, notice that a task may be within the three ranks.


    Thank you so much again

  • Sorry for not being explicit enough about explicit ...:)


    Rank 1 has Start Date = ..... and End Date = .....


    Rank 2 has Start Date = ..... and End Date = .....


    Rank 3 has Start Date = ..... and End Date = .....


    If the dates are not yet known ... the six cells where these dates will appear ... are compulsory ...!!!

    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:)

  • Yes, you are right, but I don't need them in my main layout, it is possible to create a new tab (SETTINGS) and put that data there.


    Especially because I will need them, to represent them, graphically, on lines 11, 12 and 13.


    Thanks again

    Jorge

  • Hi again,


    Correndo o risco de me repetir ....


    Rank 1 has Start Date = ..... and End Date = .....


    Rank 2 has Start Date = ..... and End Date = .....


    Rank 3 has Start Date = ..... and End Date = .....


    If the dates are not yet known ... the six cells where these dates will appear ... are compulsory ...!!!

    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:)

  • Rank 1 has Start Date = ..... and End Date = .....


    Rank 2 has Start Date = ..... and End Date = .....


    Rank 3 has Start Date = ..... and End Date = .....


    These are dates that have to be calculated.


    After all periods have been calculated and ranked, these dates are the first three.


    In the example I presented in the OUTPUT tab, it corresponds to the first three rows of columns J, K, L, M and N.


    In other words, after applying the "Weight" to each task, I rank the highest to the lowest and are the first three.


    I hope I have been clear

  • Since you do want to specify at least the cells addresses ...


    Unfortunately ... cannot help you any further ... :(

    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:)

  • Since you do want to specify at least the cells addresses ...


    Unfortunately ... cannot help you any further ... :(

    Hi Carim

    I'm sorry but which cell addresses do I need to specify?


    I thought I had clarified everything. :(


    I hope you continue to help in this challenge, which I know is not easy.


    Thanks

    Jorge

  • Back to your own sample file ...


    Have you realized how your REFERENCE dates in your Output sheet are indispensável ...???


    In your actual real-life workbook, it is absolutely the same ...!!!

    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


    Sorry I don't understand what's missing, in my initial example what I had was a tab (DATE) with the DATA for each task (Start Date and End Date).

    I put an OUTPUT tab to show which results I calculated manually, and so that whoever presented a solution could compare it with their own solution.


    If it is the way i want you to present the results that is failing in my real-life workbook, I added a tab where I show how the results can be presented, after studying the code I forward the results to the "PLANEAMENTO" tab myself.


    Thank you so much again

    Jorge

    Files

  • Unfortunately ... cannot help you any further ...:(


    Hope another contributor will better decode your thought process ...

    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 should go back to message # 10 ...


    and take all the time which is necessary to fully understand the mechanics of the suggested UDF ...

    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

    Bearing in mind that we are already going, in the exchange of messages # 37, I tell you that I thought you had already realized that the UDF does not work in the way you have presented, taking into account what I need.


    I know that what I am asking for is not easy, I will not give up but, it seems that you have already decided to give up .... :(


    However, I have to thank you very much for your contribution during this long exchange of messages.

    Thank you very much

    Jorge

  • Should you get a solution in the other Forum ...


    do not hesitate to post it ...


    Good Luck

    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:)

  • Carim

    As I told you, I don't give up!


    It is not because I asked for help here, or in any other forum, that I do not continue to try to solve the problem.


    I often have a solution to the problem, for which I ask for help.

    I ask for help just to see if there is a better solution or a faster solution.


    I have already managed to solve this problem in VBA, and I am trying to improve the code I have.


    Thank you for your contribution.

    Jorge