+ Reply to Thread
Results 1 to 10 of 10

Time formula to calculate compliance

  1. #1
    Registered User
    Join Date
    01-21-2009
    Location
    san jose
    MS-Off Ver
    Excel 2003
    Posts
    7

    Time formula to calculate compliance

    I have a spreadsheet that tracks authorization requests.

    Column A - time request recieved

    Column B - time stamp of when request was pended

    Column C - time request was completed.

    I am trying to find how to calculate whether we are in compliance.

    There are several scenarios:
    1. Requests should have a turn around time of less than 24 hours business hours
    1. However, if a request is pended the time between Column A and C can be up to 5 calendar days

    Therefore, I'd like to see

    1. For all requests that were not pended...were they resolved within 24 business hours (minus holidays, minus non-business hours, we work 8:30am to 5:30pm).

    2. For all requests that were pended is the time between Column A and C less than or equal to 5 calendar days...

    Any thoughts on how I can do this?

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474
    Hi manito2000,
    Maybe attach a sample workbook for somebody to work with...

  3. #3
    Registered User
    Join Date
    01-21-2009
    Location
    san jose
    MS-Off Ver
    Excel 2003
    Posts
    7

    Here's the file

    Hi Guys...thanks for your assistance...here's a sample file...

    1. It contains the request #
    2. Time recieved
    3. Time it was pended
    4. Time it was finalized

    I want to know whether the time between #2 and #3 is within 24 hours (excluding holidays and weekends)...
    Attached Files Attached Files

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Try this formula

    =IF(C2="",IF(D2-B2<5,"OK","More than 5 days"),IF((NETWORKDAYS(C2,D2,holidays)-1)*("17:30"-"09:30")+MOD(D2,1)-MOD(C2,1)<1,"OK","Less than 24 hours"))

    where holidays is a named range containing holiday dates

    I'm assuming that if there is an entry in column C2 you check whether C2 to D2 is less than 24 business hours but if there is no entry in C2 then the check become B2 to D2 which must be less than 5 days.

    Notes:

    NETWORKDAYS is part of Analysis ToolPak add-in which must be enabled.

    The above formula is appropriate where C2 and D2 are always both within business hours, if they may not be then you might require a more complex formula.

  5. #5
    Registered User
    Join Date
    01-21-2009
    Location
    san jose
    MS-Off Ver
    Excel 2003
    Posts
    7
    I tried the formulary =IF(C2="",IF(D2-B2<5,"OK","More than 5 days"),IF((NETWORKDAYS(C2,D2,holidays)-1)*("17:30"-"09:30")+MOD(D2,1)-MOD(C2,1)<1,"OK","Less than 24 hours"))

    But I am getting an error

  6. #6
    Registered User
    Join Date
    01-21-2009
    Location
    san jose
    MS-Off Ver
    Excel 2003
    Posts
    7
    Here's a sample of the excel with the error
    Attached Files Attached Files

  7. #7
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    If you use the exact formula I posted then you need to have a named range defined with holiday dates. To do that you can use

    Insert > Name > Define

    to define a specific range as holidays

    ...alternatively you can just use a range, e.g. if holiday dates are in H2:H10 then change formula to

    =IF(C2="",IF(D2-B2<5,"OK","More than 5 days"),IF((NETWORKDAYS(C2,D2,H$2:H$10)-1)*("17:30"-"09:30")+MOD(D2,1)-MOD(C2,1)<1,"OK","More than 24 hours"))

    see attached.

    If you still get an error that's probably because you don't have Analysis ToolPak installed (it's required for NETWORKDAYS).To install

    Tools > add-ins > tick "Analysis ToolPak" box

    Note: I changed "Less than 24 hours" to "More than 24 hours". I'm assuming that "24 business hours" means the equivalent of 3 business days.....
    Attached Files Attached Files
    Last edited by daddylonglegs; 01-24-2009 at 09:21 AM.

  8. #8
    Registered User
    Join Date
    01-21-2009
    Location
    san jose
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Time formula to calculate compliance

    Hello again...I messed up on the specifications!!!!!!

    What I should've said is the following:

    1. Request is recieved on A

    2. Requested is completed on C

    The time between A and B must be less than 24 hours (not business hours).
    However, if a request is recieved on Friday at 2:00PM you have until Monday at 2:00PM to complete the request.

    If the request is recieved on Monday at 7AM...the clock does not start ticking until 8:30am (start of the business day)...also if the request is recieved after hours (ie. Monday 7:30PM) then the clock does not start ticking until 8:30AM of the next business day.


    The other scenario is if a requested is PENDED then that will be the time in B. The time between A and B must be less than 24 hours...applying the same logice in the last two paragraphs. These requests tend to drag on for up to 5 days. I also want to measure if the time between A and C is = or less than 5 days.

    I am going crazy...!!!!!

    I hope I'm making some sense
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    12-27-2017
    Location
    United Kingdom
    MS-Off Ver
    2010
    Posts
    1

    Re: Time formula to calculate compliance

    I have a monitory spreadsheet for training

    Column A - Name
    Column B - Date completed
    Column C - Expiry date ( e,g fire is annually, food safety is 3years
    In column D I want to monitor compliance with the traffic light system . Green; within date, amber for training that will expire in 90 days and Red for staff who are out of date with their training. What formula do I use in D please . I will appreciate your help
    Last edited by cabiona; 12-27-2017 at 12:15 PM.

  10. #10
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Time formula to calculate compliance

    Please take a few minutes to read the forum rules, and then start your own thread.

    Thanks.
    Entia non sunt multiplicanda sine necessitate

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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