Date Overlapping Formula with Same Resources

Syed Muhafzal

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

Syed Muhafzal

@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

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.

Syed Muhafzal

@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

@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