+ Reply to Thread
Results 1 to 4 of 4

Summing # of overlapping days with lots of date ranges

  1. #1
    Registered User
    Join Date
    05-07-2013
    Location
    San Francisco
    MS-Off Ver
    Excel 2010
    Posts
    3

    Summing # of overlapping days with lots of date ranges

    I am having trouble figuring out how to sum up the # of overlapping days with lots of date ranges.

    I am using Excel 2010 and this is some of my data.
    1/14/2012 1/14/2012
    1/14/2012 1/14/2012
    1/15/2012 1/20/2012
    1/16/2012 1/20/2012
    2/4/2012 2/5/2012
    2/4/2012 2/11/2012
    2/4/2012 2/12/2012
    2/4/2012 2/13/2012
    2/4/2012 2/16/2012
    2/11/2012 2/24/2012
    2/11/2012 2/24/2012
    3/16/2012 3/17/2012
    3/24/2012 4/1/2012
    3/25/2012 4/1/2012
    3/26/2012 3/27/2012
    4/21/2012 4/30/2012
    4/21/2012 5/5/2012
    4/21/2012 5/5/2012
    4/22/2012 5/8/2012

    I need to add up all the networking days and exclude holiday. Was wondering if there is an easy way to calculate this.

    Thanks,

    Tuan

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Summing # of overlapping days with lots of date ranges

    I'm assuming that you want to count the number of different working days within the date ranges. If so that would be easiest with a helper column to keep a running total.

    If start date is in A2 and end date in B2 use this formula in C2

    =NETWORKDAYS(A2,B2,H$2:H$10)

    then in C3 copied down

    =MAX(0,NETWORKDAYS(MAX(A3,MAX(B$2:B2)+1),B3,H$2:H$10))+C2

    Assumes that you have holidays in H2:H10 (change as required) and that column A is sorted ascending as per your example
    Audere est facere

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Summing # of overlapping days with lots of date ranges

    Maybe this...

    With your dates in the range A2:B20.

    Holiday dates in the range D2:D10.

    =SUMPRODUCT(NETWORKDAYS(A2:A20+0,B2:B20+0,D2:D10))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    05-07-2013
    Location
    San Francisco
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Summing # of overlapping days with lots of date ranges

    Quote Originally Posted by daddylonglegs View Post
    I'm assuming that you want to count the number of different working days within the date ranges. If so that would be easiest with a helper column to keep a running total.

    If start date is in A2 and end date in B2 use this formula in C2

    =NETWORKDAYS(A2,B2,H$2:H$10)

    then in C3 copied down

    =MAX(0,NETWORKDAYS(MAX(A3,MAX(B$2:B2)+1),B3,H$2:H$10))+C2

    Assumes that you have holidays in H2:H10 (change as required) and that column A is sorted ascending as per your example
    This works. Thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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