Date Overlapping Formula with Same Resources

Hi Smarties,

I am working on a challenge below hoping for some help.

I would like to raise a flag (on a flag column), if

  1. Start Date and End Date of a project conflict with other Start Date and End Date. Projects are the ones with Header "0"
  2. Only if the Start Date and End Date of the projects are assigned to the same Project Manager

In the scenario below, Project 4 and 1 should raise a flag as they are both managed by the same project manager and fall between the same duration (Start Date).

Any help would be greatly appreciated.


Thanks

Syed

Tags:

Best Answer

  • Syed Muhafzal
    Syed Muhafzal Overachievers
    edited 08/09/21 Answer ✓

    @Bassam Khalil

    I might have figured out a formula that will give a count of the dates if there is a conflict based on the below criteria. Just to make sure I am not hallucinating, let me know if this makes sense?

    =COUNTIFS(Header:Header, 0, [Project Manager]:[Project Manager], [Project Manager]@row, [End Date]:[End Date], Date]@row, [Start Date]:[Start Date], <=[End Date]@row)

    Next, I can create a symbol column that will flag when a count is more than 1.

    🤨

    S

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    @Syed Muhafzal 

    I understand you criteria about assigning the project to the same man but how you define the conflict in Start & Finish date?

    Because i see that Project 1,3,4 had the same project manager and all of them are shared some duration.

    Project 4 & 3 shared 16 to 19 /08/2021

    Project 4 & 1 shared 4 to 13 /08/2021

    please define your criteria then i can create the formula for you.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Syed Muhafzal
    Syed Muhafzal Overachievers
    edited 08/08/21

    @Bassam Khalil

    Project 1 starts on 1st Aug and ends 13 Aug and will be managed by Employee 1

    Project 3 starts on 16th Aug and ends on 20th Aug and will be managed by Emp 1.

    As far as we are concerned, there is no conflict even though they are managed by the same employee as the duration of these two projects do not fall in the same period.

    Project 4 starts on 4th Aug and ends on 20th Aug and is managed by Employee 1.

    Now, this is a conflict, as Employee 1 has just started Project 1 on 1st Aug and 3 days later, having to start Project 4.

  • Syed Muhafzal
    Syed Muhafzal Overachievers
    edited 08/09/21 Answer ✓

    @Bassam Khalil

    I might have figured out a formula that will give a count of the dates if there is a conflict based on the below criteria. Just to make sure I am not hallucinating, let me know if this makes sense?

    =COUNTIFS(Header:Header, 0, [Project Manager]:[Project Manager], [Project Manager]@row, [End Date]:[End Date], Date]@row, [Start Date]:[Start Date], <=[End Date]@row)

    Next, I can create a symbol column that will flag when a count is more than 1.

    🤨

    S

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!