Forum Discussion
Count date overlaps
Hi experts. Hope you can help!
I have a data set as follows:
Store | Project 1 |
| Project 2 |
| Project 3 |
| Project 4 |
|
|
|
|
| Start | End | Start | End | Start | End | Start | End | Project Totals | Min Wk | Max Wk |
1345 | 07/10/24 | 28/10/24 |
|
| 14/10/24 | 04/11/24 | 14/10/24 | 21/10/24 | 3 | 07/10/24 | 04/11/24 |
1111 |
|
|
|
| 07/10/24 | 28/10/24 |
|
| 1 | 07/10/24 | 28/10/24 |
981 | 04/11/24 | 11/11/24 | 07/10/24 | 28/10/24 | 21/10/24 | 11/11/24 | 04/11/24 | 11/11/24 | 4 | 07/10/24 | 11/11/24 |
2210 | 21/10/24 | 28/10/24 |
|
| 04/11/24 | 11/11/24 |
|
| 2 | 21/10/24 | 11/11/24 |
3401 |
|
| 07/10/24 | 28/10/24 |
|
|
|
| 1 | 07/10/24 | 28/10/24 |
What I want to do is after the Max WK column, add another that counts how many projects overlap in dates by store.
For example. Store 1345 has 3 projects in total and all 3 are overlapping in dates. Answer 3
Store 1111 only has one project and therefore naturally there are no other projects overlapping. Answer 0
Based on my column structure, is this possible?
Thanks for your help!
See the attached version
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
- matt0020190Brass Contributor
HansVogelaar Thanks for your quick response. See attached. I hope it makes sense.
I am trying to get a number of projects "live" in each given week as you will see in the example.My example works for the starting week, but I want the formula to check between the date ranges so that every week shows the total number of projects active/live
I'll look at it tomorrow (Friday)