+ Reply to Thread
Results 1 to 4 of 4

Formula to calculate service level compliance

  1. #1
    Registered User
    Join Date
    10-29-2008
    Location
    Norwich
    MS-Off Ver
    365
    Posts
    10

    Post Formula to calculate service level compliance

    Dear Experts

    I've had a good search but can't find a solution. I'd like a formula to calculate service level completion times in conjunction with service availability. For example, service availability & levels could be

    Mon-Fri, 08:00-18:00
    Sat-Sun, 08:00-16:00
    Bank hols, Unavailable

    Service levels
    5 working days
    3 working days
    2 working days
    4 hours
    2 hours
    1 hour
    30 mins

    Ideally, I'd like the formula to reference the availability & service levels so that it can be adapted for other contracts.

    Example spreadsheet attached. Can you please help?
    Attached Files Attached Files
    Last edited by ljerromes; 04-10-2016 at 11:26 AM. Reason: Spelling

  2. #2
    Registered User
    Join Date
    02-07-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Formula to calculate service level compliance

    you need to compute no. of days & hrs in between the call logging and service date and then compare it with service level reflected to say which category of service level as also whether pass / fail - is my understanding right ?

  3. #3
    Registered User
    Join Date
    10-29-2008
    Location
    Norwich
    MS-Off Ver
    365
    Posts
    10

    Re: Formula to calculate service level compliance

    Thanks for your response. Yes I think we are on the same lines.

    Firstly, I need to be able calculate the 'due by - date & time', by the adding any service level (time span), to the task created date. The tricky part is making sure it only counts the time for the relevant service period. I.E. A task starting at 08/04/2016 13:44, service level - 3 days, in this example the 'due by time & date' would be 11/04/2016 09:44

    Secondly, once the completed date is populated it needs to display PASS or FAIL to indicate whether it is over the 'due by - time and date'.

    I’ve updated the spreadsheet in the 1st post with some further info. Thanks.

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,336

    Re: Formula to calculate service level compliance

    I am not sure I grasp the concepts. Try this in C2
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and this in F2
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Does this do what you want?

    Edited The first formula. Accounts for holidays and uses the working hours table.
    Last edited by FlameRetired; 04-10-2016 at 07:13 PM.
    Dave

+ 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. Time formula to calculate compliance
    By manito2000 in forum Excel General
    Replies: 9
    Last Post: 12-27-2017, 12:15 PM
  2. [SOLVED] Service level template
    By henrikf1 in forum Excel General
    Replies: 9
    Last Post: 01-27-2016, 04:06 AM
  3. Formula to calculate compliance
    By patfle in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-16-2013, 11:54 AM
  4. Formula to calculate compliance
    By patfle in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-16-2013, 11:41 AM
  5. Replies: 4
    Last Post: 06-13-2012, 01:52 PM
  6. Service Level
    By Jemma1982 in forum Excel General
    Replies: 1
    Last Post: 11-27-2008, 07:35 PM
  7. Service Level Agreements
    By Mustafa Hussein in forum Excel - New Users/Basics
    Replies: 0
    Last Post: 02-12-2005, 07:06 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