+ Reply to Thread
Results 1 to 6 of 6

Count Net Days of Overlapping Dates

  1. #1
    Registered User
    Join Date
    10-14-2015
    Location
    East Coast
    MS-Off Ver
    2013
    Posts
    69

    Count Net Days of Overlapping Dates

    All,

    I'm struggling with the following. I have a table that includes a list of help desk tickets for a number of systems that have Date Recorded and Date Resolved dates. Each help desk ticket also includes a column for total days the ticket was open (e.g., Date Resolved - Date Recorded). Some tickets are "open" and do not have resolved dates - in which case the total downtime for that ticket would be today - date recorded. The tickets are also categorized as "down", "degraded", or "up". I'd only like to calculate the "down" ticket days.

    I'm wanting to count the total NET down days for the systems and sub-areas included for "down" tickeets. For example, if for system X section 1, ticket 1 was opened 5/10/18 and closed on 5/12/18, and ticket two was opened 5/10/18 and was closed on 5/15/18, the total net days for system X section 1 would be 6 days (e.g., 5/10/18 to 5/15/18) and not 9 days (e.g., 5/10/18-5/12/18 [3 days] + 5/10/18-5/15/18 [6]).

    I was wondering if there was any way to accomplish this in a button-activated macro. I've attached an example workbook to provide additional clarity.

    Best,
    Chris
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: Count Net Days of Overlapping Dates

    See attached sample.
    Cells F12:F16 give you the result you want.
    I have used E12:E16 as helper cells to simplify the formula in F12:F16.
    E12:E16 represent number of times Down appears for each Sector, Section combo.
    If it's 0 then number of days in F12:F16 gets zeroed out.

    On thing I had to do was to put today's date in F5.
    This one didn't have a date so you are supposed to use today's date to figure out net days.

    I am sure someone will offer more concise solution but hopefully this will help you somewhat.

    Formula in F12:F16

    HTML Code: 
    it's an array formula entered with ctrl+shft+ent.
    1. Click on the * Add Reputation if you think this helped you
    2. Mark your thread as SOLVED when question is resolved

    Modytrane

  3. #3
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: Count Net Days of Overlapping Dates

    If you don't want to use helper cells, here's the formula combining E12 and F12.
    You can enter this in F12 and copy down.

    HTML Code: 
    P.S: It's an array formula.

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Count Net Days of Overlapping Dates

    VBA
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    10-14-2015
    Location
    East Coast
    MS-Off Ver
    2013
    Posts
    69

    Re: Count Net Days of Overlapping Dates

    Wow! Great solutions modytrane & jindon! This works perfectly!

    Jindon, is there a way to add one more wrinkle to your script? Some tickets are submitted with "All sections" in the second column. For example, Sector 1 may have a ticket come in that says "All Sections". The downtime would then apply to all sections within Sector 1 (with the same overlapping logic applying).

    Is this possible? I've re-attached the example sheet as reference.

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Count Net Days of Overlapping Dates

    Change to
    Please Login or Register  to view this content.

+ 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. Replies: 8
    Last Post: 02-27-2018, 05:02 AM
  2. Late count based on number of days between two dates for a list of dates
    By Keats713 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-03-2017, 11:03 PM
  3. Late count based on number of days between two dates for a list of dates
    By Keats713 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-03-2017, 06:35 PM
  4. Late count based on number of days between two dates for a list of dates
    By Keats713 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-03-2017, 02:19 PM
  5. Count Overlapping dates in multiple date ranges with a criteria
    By jenn.murphy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-05-2016, 03:07 PM
  6. Replies: 15
    Last Post: 06-17-2016, 01:59 AM
  7. 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

Tags for this Thread

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