# Count overlap between days multiple dates

1. ## Count overlap between days multiple dates

Looking to calculate any instance within a grouping of overlap. Each grouping has a sub total. I was able to find the 1st overlap within the grouping but if there were multiple overlaps within a grouping I'm stuck.
For instance

1- 6/1/2020 6/19/2020
2- 6/1/2020 6/6/2020
3- 6/10/2020 6/30/2020
4- 6/16/2020 6/24/2020
5- 6/24/2020 6/30/2020

1 and 2 have a 7 day overlap
1 and 3 have a 10 day overlap
1 and 4 have a 4 day overlap

Would need a formula that could be broken down in a few steps as the calculations need to be validated by another person.

I'd attach the file but for some reason I'm unable to.

3. ## Re: Count overlap between days multiple dates

Hello pea343 and Welcome to Excel Forum.
I am guessing that you want a formula to populate column R corresponding to the cells marked "Total" in column A and based on the values in column Q.
If that is the case please paste the following into cell R6 and copy down to cell R73:
Formula:
`Please Login or Register  to view this content.`

Remember to select Fill without formatting while R6:R73 are still selected to preserve the yellow highlighting in those cells.
If my guess is not correct then please manually place some of the values along with an explanation of how the values are derived. One of the contributors may then be able to produce a formula/code to automate the process.
Let us know if you have any questions.

4. ## Re: Count overlap between days multiple dates

JeteMc

Thank you for the response! Although the formula you gave me did give me the accumulated overlap, it is not quite what I was looking for.

1- 6/1/2020 6/19/2020
2- 6/1/2020 6/6/2020
3- 6/10/2020 6/30/2020
4- 6/16/2020 6/24/2020
5- 6/24/2020 6/30/2020

1 and 2 have a 7 day overlap
1 and 3 have a 10 day overlap
1 and 4 have a 4 day overlap

The result(s) I'm looking for would identify the 3 overlaps and calculate that. The only thing I've been able to identify is the overlap of (for instance) line 1 and line 2 and then line 3 looks at line 2 but doesn't identify the overlap to line 1. It is entirely possible that this has to be a multiple formula solution and it's too complex of a formula that I wouldn't be able to teach another team member

5. ## Re: Count overlap between days multiple dates

Here is a file based on the dates used in post #4.
The formula used to calculate overlap between the first set of dates and those subsequent is:
Formula:
`Please Login or Register  to view this content.`

The formula used for total overlap is the same as in post #3.
If you'll let us know what about the formulas is too complex to teach, we may be able to help with explanations.
Let us know if you have any questions.

6. ## Re: Count overlap between days multiple dates

I think I'll be able to explain/teach the formula from your last post. I'll at least give it a go and if I have questions I'll reach out again. Until then, I'll close the thread. Thanks for all the help!

7. ## Re: Count overlap between days multiple dates

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts