+ Reply to Thread
Results 1 to 7 of 7

Count overlap between days multiple dates

  1. #1
    Registered User
    Join Date
    04-08-2019
    Location
    USA
    MS-Off Ver
    16
    Posts
    4

    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.
    Last edited by pea343; 09-30-2020 at 02:58 PM.

  2. #2
    Registered User
    Join Date
    04-08-2019
    Location
    USA
    MS-Off Ver
    16
    Posts
    4

    Re: Count overlap between days multiple dates

    attempt to add attachment
    Attached Files Attached Files

  3. #3
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,695

    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: copy to clipboard
    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.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  4. #4
    Registered User
    Join Date
    04-08-2019
    Location
    USA
    MS-Off Ver
    16
    Posts
    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. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,695

    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: copy to clipboard
    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.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    04-08-2019
    Location
    USA
    MS-Off Ver
    16
    Posts
    4

    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. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,695

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Count How Often Multiple Values Overlap Each Other
    By nevi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-24-2019, 05:29 PM
  2. Replies: 2
    Last Post: 01-03-2019, 05:36 PM
  3. [SOLVED] Count Number of Days excluding overlapped dates between multiple dates
    By givemeaccessexcel in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-21-2018, 06:43 PM
  4. Overlap of Multiple Time Intervals in Days of the Week
    By Jayden933 in forum Excel General
    Replies: 0
    Last Post: 07-01-2015, 04:32 PM
  5. Replies: 1
    Last Post: 09-30-2014, 04:19 PM
  6. [SOLVED] Count Start and End Dates that Overlap Specified Date Range
    By DigDoug in forum Excel General
    Replies: 8
    Last Post: 06-11-2014, 09:46 AM
  7. How to count the days when the periods overlap
    By Nathalie in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-07-2012, 09:46 AM

Bookmarks

Posting Permissions

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