+ Reply to Thread
Results 1 to 12 of 12

counting number of days in overlapping date ranges

  1. 07-16-2018, 04:37 PM #1
    afgi
    afgi is offline
    Forum Contributor
    Join Date
    09-13-2010
    Location
    Poland
    MS-Off Ver
    365
    Posts
    204

    counting number of days in overlapping date ranges

    hi,

    I would like to count number of single days in multiple overlapping and nonoverlapping date ranges.

    For example:

    Nowy obraz (1).png
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 07-16-2018, 05:42 PM #2
    FDibbins
    FDibbins is offline
    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,933

    Re: counting number of days in overlapping date ranges

    are the days always sequential in col A?
    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 With QuoteRegister To Reply
  3. 07-16-2018, 08:31 PM #3
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: counting number of days in overlapping date ranges

    Try in D2 and fill down. (Done in E2 of attached.)
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by FlameRetired; 07-16-2018 at 08:34 PM.
    Dave
    Reply With QuoteRegister To Reply
  4. 07-16-2018, 10:08 PM #4
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: counting number of days in overlapping date ranges

    This is shorter.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 07-17-2018, 05:30 AM #5
    afgi
    afgi is offline
    Forum Contributor
    Join Date
    09-13-2010
    Location
    Poland
    MS-Off Ver
    365
    Posts
    204

    Re: counting number of days in overlapping date ranges

    no, these are random
    Reply With QuoteRegister To Reply
  6. 07-17-2018, 05:40 AM #6
    afgi
    afgi is offline
    Forum Contributor
    Join Date
    09-13-2010
    Location
    Poland
    MS-Off Ver
    365
    Posts
    204

    Re: counting number of days in overlapping date ranges

    HI,

    THANKS FOR REPLAY. Your second formula works fine. Are you able to add one more condition. example in column A with 2 products.

    p.s. are you able to include in the formula possiblitity of empty rows?
    Nowy obraz.png
    Attached Files Attached Files
    Last edited by afgi; 07-17-2018 at 06:41 AM.
    Reply With QuoteRegister To Reply
  7. 07-17-2018, 05:54 AM #7
    Sadath31
    Sadath31 is offline
    Valued Forum Contributor Sadath31's Avatar
    Join Date
    03-02-2011
    Location
    Dammam, Saudi Arabia
    MS-Off Ver
    Office 365
    Posts
    452

    Re: counting number of days in overlapping date ranges

    Hi
    Why not this
    First Row =B2-A2
    2nd row on wards =B3-MAX(A3,MAX($A$2:B2))
    Reply With QuoteRegister To Reply
  8. 07-17-2018, 06:18 AM #8
    afgi
    afgi is offline
    Forum Contributor
    Join Date
    09-13-2010
    Location
    Poland
    MS-Off Ver
    365
    Posts
    204

    Re: counting number of days in overlapping date ranges

    I am trying to count number of days. doesnt matter if it's overlapping nor nonoverlapping - I dont want to count the same days twice or miss any
    Reply With QuoteRegister To Reply
  9. 07-17-2018, 06:18 AM #9
    afgi
    afgi is offline
    Forum Contributor
    Join Date
    09-13-2010
    Location
    Poland
    MS-Off Ver
    365
    Posts
    204

    Re: counting number of days in overlapping date ranges

    Quote Originally Posted by Sadath31 View Post
    Hi
    Why not this
    First Row =B2-A2
    2nd row on wards =B3-MAX(A3,MAX($A$2:B2))
    I am trying to count number of days. doesnt matter if it's overlapping nor nonoverlapping - I dont want to count the same days twice or miss any
    Reply With QuoteRegister To Reply
  10. 07-17-2018, 01:59 PM #10
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: counting number of days in overlapping date ranges

    Quote Originally Posted by afgi View Post
    HI,

    THANKS FOR REPLAY. Your second formula works fine. Are you able to add one more condition. example in column A with 2 products.
    p.s. are you able to include in the formula possiblitity of empty rows?
    Try this replacement.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    You'll need to give us examples and expected results for the empty rows part.
    Last edited by FlameRetired; 07-17-2018 at 02:02 PM.
    Reply With QuoteRegister To Reply
  11. 07-18-2018, 05:33 AM #11
    soledad
    soledad is offline
    Forum Contributor
    Join Date
    11-21-2013
    Location
    zimbabwe
    MS-Off Ver
    Excel 2003
    Posts
    124

    Re: counting number of days in overlapping date ranges

    Formula for your request is
    Cell E2
    Please Login or Register  to view this content.
    Confirmed with Ctrl+Shift+Enter
    Reply With QuoteRegister To Reply
  12. 07-18-2018, 03:02 PM #12
    afgi
    afgi is offline
    Forum Contributor
    Join Date
    09-13-2010
    Location
    Poland
    MS-Off Ver
    365
    Posts
    204

    Re: counting number of days in overlapping date ranges

    Quote Originally Posted by FlameRetired View Post
    Try this replacement.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    You'll need to give us examples and expected results for the empty rows part.
    Quote Originally Posted by soledad View Post
    Formula for your request is
    Cell E2
    Please Login or Register  to view this content.
    Confirmed with Ctrl+Shift+Enter
    Soledad, your formula works perfectly

    FlameRetired thanks for your help

    file attached
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Calculating # of Overlapping days with several date ranges and conditions
    By ZafferAhmed in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-30-2021, 05:44 AM
  2. find Sum of # of days w/o counting overlapping days twice with multiple paramaters
    By civ1979 in forum Excel General
    Replies: 15
    Last Post: 06-17-2016, 01:59 AM
  3. Count number of overlapping days in multiple date ranges
    By tarsonis in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 05-08-2015, 03:22 PM
  4. Calculating # of Overlapping days with several date ranges.
    By CarlSVM in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-06-2014, 07:09 AM
  5. Summing # of overlapping days with lots of date ranges
    By tvnsf in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-08-2013, 01:48 PM
  6. Counting and summing the total number of overlapping days in two periods
    By kylienut in forum Excel General
    Replies: 0
    Last Post: 07-30-2012, 04:51 PM
  7. Counting Continuous Days within overlapping Date Ranges
    By mgaworecki in forum Excel General
    Replies: 3
    Last Post: 09-07-2011, 08:33 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1