Results 1 to 12 of 12

Response time in hours and minutes excluding weekends and holidays

Threaded View

  1. #1
    Registered User
    Join Date
    07-02-2018
    Location
    Spokane, WA
    MS-Off Ver
    2016
    Posts
    6

    Response time in hours and minutes excluding weekends and holidays

    I am struggling to develop a formula to calculate response time for technical support calls at our company. Below are a few examples from my data set and the current formula I am using.

    Incoming date/time Response date/time Total [h]:mm:ss
    1/02/18 07:54:00 1/05/18 08:32:00 27:38:00
    1/02/18 08:36:00 1/02/18 08:39:00 0:03:00
    1/02/18 08:58:00 1/02/18 09:05:00 0:07:00
    1/02/18 09:08:00 1/03/18 10:22:00 10:14:00
    1/02/18 09:12:00 1/02/18 10:19:00 1:07:00
    1/02/18 09:22:00 1/02/18 09:31:00 0:09:00


    Formula - Current

    R = incoming date and time for call
    S = response date and time for call
    W = work time end
    V = work time start

    =(NETWORKDAYS(R2,S2)-1)*($W$2-$V$2)+(MOD(S2,1)-MOD(R2,1))

    The problem I am running into is calls can come in outside of the normal business hours set in W and V (8am-5pm) and on the weekends. Our support engineers sometimes show up early and respond to calls before 8am or stay late and respond to calls after 5pm. I want to calculate our response time for normal business hours, so if a call comes in on the weekend, the incoming time should be calculated for Monday at 8am start and end no matter what time it is for the response date/time. My current formula errors (with a negative) for the following scenario


    Incoming date/time Response date/time
    1/13/18 11:49:00 1/15/18 10:51:00
    1/23/18 17:00:00 1/24/18 07:52:00
    1/27/18 09:43:00 1/29/18 09:15:00
    1/31/18 17:36:00 2/01/18 08:15:00
    2/01/18 19:11:00 2/02/18 08:39:00
    2/02/18 15:35:00 2/03/18 14:00:00
    2/04/18 20:54:00 2/05/18 08:29:00
    2/04/18 21:09:00 2/05/18 08:30:00
    2/05/18 18:56:00 2/06/18 08:14:00
    2/28/18 22:53:00 3/01/18 08:26:00
    3/03/18 12:28:00 3/05/18 07:55:00
    3/06/18 17:30:00 3/07/18 07:57:00
    3/06/18 21:58:00 3/07/18 08:13:00
    3/07/18 17:33:00 3/08/18 07:59:00
    3/13/18 17:42:00 3/14/18 08:02:00
    3/13/18 21:54:00 3/14/18 10:07:00
    3/23/18 19:16:00 3/26/18 08:48:00
    3/26/18 21:56:00 3/27/18 08:30:00
    3/28/18 22:10:00 3/29/18 09:08:00
    4/04/18 17:43:00 4/05/18 08:00:00
    4/04/18 17:49:00 4/05/18 08:06:00
    4/04/18 18:43:00 4/05/18 08:48:00
    4/10/18 23:44:00 4/11/18 08:54:00
    4/11/18 19:57:00 4/12/18 07:44:00
    4/11/18 21:02:00 4/12/18 07:44:00
    4/11/18 23:25:00 4/12/18 07:46:00
    4/13/18 13:33:00 4/16/18 00:00:00
    4/16/18 10:37:00 4/16/18 00:00:00
    4/20/18 17:23:00 4/23/18 07:42:00
    4/20/18 19:46:00 4/23/18 07:55:00
    4/21/18 22:39:00 4/24/18 10:17:00
    4/25/18 21:58:00 4/26/18 09:55:00
    4/28/18 16:57:00 4/30/18 11:33:00
    4/30/18 23:18:00 5/01/18 10:32:00
    5/03/18 17:09:00 5/04/18 08:03:00
    5/10/18 16:57:00 5/11/18 07:38:00
    5/12/18 14:41:00 5/14/18 07:56:00
    5/31/18 20:04:00 6/01/18 08:20:00
    5/31/18 21:18:00 6/01/18 08:31:00
    6/04/18 12:01:00 6/04/18 12:00:00
    6/07/18 17:49:00 6/08/18 08:10:00
    6/11/18 18:14:00 6/12/18 08:26:00
    6/12/18 23:51:00 6/13/18 08:03:00
    6/24/18 21:35:00 6/25/18 07:53:00
    6/24/18 22:00:00 6/25/18 08:08:00
    6/24/18 22:14:00 6/25/18 08:09:00

    Any advice would be much appreciated on this!

    EDIT: I have added the Excel workbook I am working on with examples of correct and incorrect values.
    Attached Files Attached Files
    Last edited by ACBenson86; 07-18-2018 at 02:18 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How To Calculate Net Work Hours Between Two Dates Excluding Weekends Or Holidays?
    By pavankumarbangaru in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-23-2018, 11:52 AM
  2. Formula to calculate SLA by excluding non-business hours, weekends and holidays
    By kokilak@hcl.com in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-04-2016, 11:42 PM
  3. Replies: 0
    Last Post: 01-30-2014, 01:22 AM
  4. [SOLVED] Working Hours - excluding weekends, holidays
    By CJENKSY in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-05-2013, 02:12 AM
  5. [SOLVED] Elapsed Days Hours Minutes Excluding Weekends and Holidays
    By moshjosh in forum Excel General
    Replies: 7
    Last Post: 12-10-2012, 08:39 AM
  6. Replies: 4
    Last Post: 08-10-2012, 11:41 AM
  7. Replies: 7
    Last Post: 01-11-2011, 06:26 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1