+ Reply to Thread
Results 1 to 9 of 9

Determine workdays between multiple date ranges, excluding overlap and gaps

  1. #1
    Registered User
    Join Date
    07-28-2014
    Location
    CA
    MS-Off Ver
    2010
    Posts
    4

    Determine workdays between multiple date ranges, excluding overlap and gaps

    Hello all,

    I have multiple date ranges, the start and end date are in different columns (say B and C).
    Example:

    2/10-2/12
    2/11-2/14
    2/18-2/21

    So to some extent I was using networkdays with min from the column B range and max from column C range, but while that avoids double counting the overlap between 2/10 to 2/12 and 2/11 to 2/14, it counts the gap between 2/14 to 2/18, which I need it to not do. On the other hand, if I do networkdays for each and sum them, I end up counting overlap days more than once.

    Thank you in advance for any tips and suggestions.
    Last edited by Taranis; 07-28-2014 at 09:29 PM. Reason: Clarity

  2. #2
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Determine workdays between multiple date ranges, excluding overlap and gaps

    Are they always in sequence like that, or could they jump around? eg:
    2/10-2/12
    2/11-2/14
    2/18-2/21
    2/09-2/11
    Please add reputation by clicking on the * if I have helped.
    Please mark the thread SOLVED if your issue has been resolved.
    Thanks, Glenn.

  3. #3
    Registered User
    Join Date
    07-17-2012
    Location
    Delhi
    MS-Off Ver
    Excel 2007
    Posts
    61

    Re: Determine workdays between multiple date ranges, excluding overlap and gaps

    try this..

    sandysolved Notes.xlsx

  4. #4
    Registered User
    Join Date
    07-28-2014
    Location
    CA
    MS-Off Ver
    2010
    Posts
    4

    Re: Determine workdays between multiple date ranges, excluding overlap and gaps

    Gak67: They could jump around, and there's also a possibility of same dates as a starting or ending point.
    ex.
    2/10-2/12
    2/10-2/15
    2/12-2/15

    Sam51285: Is that the correct file? I'm not seeing the relation.

  5. #5
    Registered User
    Join Date
    07-28-2014
    Location
    CA
    MS-Off Ver
    2010
    Posts
    4

    Re: Determine workdays between multiple date ranges, excluding overlap and gaps

    It may be possible to prevent future jumping around by informing users, and have the information be sorted by start date.

  6. #6
    Registered User
    Join Date
    07-28-2014
    Location
    CA
    MS-Off Ver
    2010
    Posts
    4

    Re: Determine workdays between multiple date ranges, excluding overlap and gaps

    Is there perhaps a better way to go about this than two columns as start/end dates?

  7. #7
    Registered User
    Join Date
    04-17-2016
    Location
    chicago
    MS-Off Ver
    MS Office 2013
    Posts
    1

    Re: Determine workdays between multiple date ranges, excluding overlap and gaps

    Did we get the solution for this one, I am also not able to get the network days days between multiple date ranges by avoiding overlapping and gaps
    Example of question -
    A 02/10/17 03/08/17 (Format is MM/DD/YY)
    A 03/04/17 03/07/17
    A 04/01/17 04/12/17
    B -----------
    B -------------

    Required is-
    A - 27 days
    B -- XXX days


    Thanks,
    Parag

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Determine workdays between multiple date ranges, excluding overlap and gaps

    Hi Taranis. Welcome to the forum.

    Try this array entered formula.
    If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter. In C1:
    Formula: copy to clipboard
    Please Login or Register  to view this content.




    A
    B
    C
    1
    2/10/2017
    2/12/2017
    10
    2
    2/11/2017
    2/14/2017
    3
    2/18/2017
    2/21/2017
    4
    2/9/2017
    2/11/2017
    Dave

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Determine workdays between multiple date ranges, excluding overlap and gaps

    paragmadpuwar welcome to the forum

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ 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. [SOLVED] determine total overlap time when there are gaps in overlap (4 date ranges)
    By miriambender in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-12-2014, 11:58 PM
  2. Replies: 2
    Last Post: 08-16-2013, 10:18 AM
  3. Date Ranges with Gaps - Finding Corresponding Maximum
    By thankfulsupplicant in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-10-2013, 07:43 PM
  4. workdays between (excluding holidays)
    By imaccormick1990 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-22-2013, 08:26 AM
  5. Determining the % of overlap in two date ranges
    By eddienole in forum Excel General
    Replies: 3
    Last Post: 07-29-2011, 04:45 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1