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
- Start Date and End Date of a project conflict with other Start Date and End Date. Projects are the ones with Header "0"
- 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
Best Answer
-
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
-
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.
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"
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!