+ Reply to Thread
Results 1 to 6 of 6

Help: conditionals with date/time. E.g., Need duration only if time falls in M-F 6am-5pm.

  1. #1
    Registered User
    Join Date
    09-21-2016
    Location
    Santa Cruz, CA
    MS-Off Ver
    Excel for Mac 2011, v.14.6.0
    Posts
    8

    Help: conditionals with date/time. E.g., Need duration only if time falls in M-F 6am-5pm.

    Hello all! I'm trying to calculate the outage duration for when a website is down. We need to know, of the total outage duration, which hours fall into "PEAK hours" and which hours fall within "NON-PEAK Hours" criteria range.
    Spreadsheet attached. See the sheet "Testing" in this file.

    In the example, sheet "Testing" row 2
    The outcome should be

    Total duration hours for peak and non-peak time frames
    PEAK (duration) 2:00
    NON-PEAK (duration) 13:00
    15:00 <--Grand total of entire duration

    The example uses the following start and stop times:
    Start of outage | End of outage
    9/27/16 16:00 | 9/28/16 7:00


    Cell B9 is 2 hours, because within the total outage duration, 1 hour falls within the "PEAK" range criteria (9/27 4pm - 5pm), and on the 2nd day 9/28, another hour also falls within the PEAK criteria, (6am - 7am) for a total of a 2 hour duration for the PEAK category.
    Cell B10 is 13 hours because the remainder of the outage hours fall within the NON-PEAK criteria
    However, if 9/28/16 was a holiday, then that 2nd hour from 6am-7am would have to be counted in NON-PEAK hours instead of PEAK hours (so, the new totals would be 1 hour peak, 14 hours non-peak).

    Category criteria
    PEAK HOURS = M-F 6am - 5pm
    NON-PEAK HOURS = all other hours/days (5pm and on for M-F) and weekends (sat & sun) and any holidays (which I will list in a column)

    Any help MUCH appreciated, I've been quite stuck for a while and keep trying various options and researching....I've looked into several functions (e.g., TIME, TEXT, WORKDAY, etc)
    If the outage range did not span days, and were only within 1 day (start date and stop date were the same), then this would work: =MIN(D6,TIME(17,0,0))-MAX(C6,TIME(6,0,0)) to calculate PEAK category, but since outages can span days, this doesn't work.
    I think I need a conditional formula that says to give the duration based on the PEAK and NONPEAK criteria.

    File attached here.
    Attached Files Attached Files
    Last edited by CathTyner; 09-28-2016 at 12:20 PM.

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,578

    Re: Help: conditionals with date/time. E.g., Need duration only if time falls in M-F 6am-5

    Looked at the Google Sheets file and not saying that I can provide a solution, however I can tell you how to upload an Excel spreadsheet/workbook to this site so that it can be worked on, as there isn't a way I could find to test formulae on the Testing page in Google Sheets.
    To attach a sample workbook click on the GO ADVANCED button and then scroll down to Manage Attachments to open the upload window.
    Let me 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.

  3. #3
    Registered User
    Join Date
    09-21-2016
    Location
    Santa Cruz, CA
    MS-Off Ver
    Excel for Mac 2011, v.14.6.0
    Posts
    8

    Re: Help: conditionals with date/time. E.g., Need duration only if time falls in M-F 6am-5

    Thank you! File attached.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,578

    Re: Help: conditionals with date/time. E.g., Need duration only if time falls in M-F 6am-5

    This solution modifies two of the formulae found in this article.
    Notes: The calculation of G2 and H2 is respectively changed to: =A2+B2 and =C2+D2
    Two cells have been added: X2 is the Peak Start and Y2 is the Peak End, this allows flexibility, as opposed to hard coding, should these times change at a later date.
    The first modified formula, which is the main formula in the article, calculates the peak hours and reads:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The second formula takes a formula, called the 24 hour variation in the article, and subtracts the peak hours to calculate non-peak hours and reads:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Let me know if you have any questions.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    09-21-2016
    Location
    Santa Cruz, CA
    MS-Off Ver
    Excel for Mac 2011, v.14.6.0
    Posts
    8

    Red face Re: Help: conditionals with date/time. E.g., Need duration only if time falls in M-F 6am-5

    JetMc!!!!

    WOW!!!

    I'm so impressed and even more GRATEFUL. THANK YOU!!! I have a huge smile on my face!

    This works great.
    One problem.

    The problem is this, for example:
    If you change P2 holiday to 9/28/16,
    Then as expected, 'peak hours' changes from 2 to 1 - correct.
    And total duration remains correct, at 15 hours.
    However, 'non-peak hours' becomes 7.
    But that is incorrect, because non-peak hours are defined as (5pm-on for M-F, weekends, and holidays) = 14 in this case.
    Because your 'peak hours' formula is so AWESOME, I think the solution for defining non-peak hours
    will simply be 'total duration' - 'peak hours.'

    I will have to run more test cases, but this is looking great.
    I can't thank you enough!!

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,578

    Re: Help: conditionals with date/time. E.g., Need duration only if time falls in M-F 6am-5

    You are both Welcome and Correct. For the benefit of future readers of this thread a formula, in S2, that would subtract 'peak hours' from 'total duration' is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If after further testing you find that the solution works for all cases, please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. 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. How to find the duration between date & time.
    By irfanpsr in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-18-2016, 06:42 AM
  2. [SOLVED] How to see if a specific date and time falls between two date/time fields
    By BlueberryTech in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-06-2014, 09:02 AM
  3. Replies: 11
    Last Post: 08-05-2014, 05:02 PM
  4. [SOLVED] Date and Time duration formula
    By tep1981 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-17-2013, 03:16 AM
  5. Weeks left in a month after a certain date for a certain duration of time
    By mknapp21 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-10-2012, 03:34 PM
  6. Determining if date/time falls within spanned time
    By Bytor47 in forum Excel General
    Replies: 5
    Last Post: 09-15-2009, 01:55 PM
  7. [SOLVED] Date/time Duration
    By zjay in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-24-2005, 06:06 AM

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