Count overlapping time periods with condition

  • Hi all,

    I have got an Excel problem that I am not really able to solve by myself and therefore kindly request your support.

    I would like to find out on how many parallel projects our employees are staffed.
    In doing so, I have the start and end date of each project and on which project which employee is staffed in which role (PM = project manager, CT = core team, ET = extended team).

    I am able to find out which projects overlap by using the SUMPRODUCT- formula (SUMPRODUCT((I9<projectend)*(I10>=projectstart))>1. Unfortunately, this formula only compares all projects with each other and provides the information if a project overlaps with with another project in the list. It does not give back the information if the projects the employee is staffed on overlap with another project the employee is also staffed on (regardless of role). However, this is exactly the information that I need and I have been thinking about this problem for a while and have not come up with a suitable and, more importantly, workable solution.

    Do you have any ideas? I would really appreciate if someone could provide a solution approach to this problem .

    I have attached a dummy file visualizing the structure of my Excel sheet.

    Thank you so much!


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!