Results 1 to 6 of 6

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

Threaded View

  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.

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