Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Need a formula to prepare SLA report

  1. #1

    Need a formula to prepare SLA report



    Register for a FREE account, and/
    or Log in to avoid these ads!

    SLA
    Critical = 4 hours
    High=8 hours
    Medium= 72 hours
    Low=120 hours

    If the ticket is closed within the SLA then the result should be “Within SLA” else “Exceeding SLA”

    Attached is the only data available through the system. I tried to use the status, priority, created date and last modified date to achieve this but no luck.
    Yellow cells indicate data extract from the system.
    Appreciate if someone could help me with the available data.
    Attached Files Attached Files

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    I dont' follow what you're asking. You already seem to have formulas to get that in the Violation column?
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Do you have a formula to exclude business hours. I want to exclude the hours from 5 PM - 8:00 AM.

  4. #4
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    791
    Articles
    0
    Excel Version
    2010
    What about weekends and public holidays ? Shouldn't you exclude these as well?

  5. #5
    Hi ethelk2044

    Do you mean the total hours between 2 of your dates. Perhaps the following!

    =(NETWORKDAYS(StartDate/Time,EndDate/Time-1,Holidays)*("17:00"-"08:00")+(MOD(EndDate/Time,1)-MOD(StartDate/Time,1)))*24

  6. #6

    Need a formula to prepare SLA report

    I need to exclude the hours from 5 PM till 8 AM Monday - Thursday. I also need to exclude the hours from 5 PM on Friday till 8 AM on Monday.


    Quote Originally Posted by Kevin@Radstock View Post
    Hi ethelk2044

    Do you mean the total hours between 2 of your dates. Perhaps the following!

    =(NETWORKDAYS(StartDate/Time,EndDate/Time-1,Holidays)*("17:00"-"08:00")+(MOD(EndDate/Time,1)-MOD(StartDate/Time,1)))*24

  7. #7
    So what would be your expected result for the date and times below be!

    29/03/2013 09:00:00 - 02/04/2013 14:00:00

  8. #8
    I found a formula that works but do not understand it.I want to learn what is going on so I will be able to create other formulas. Can someone break down what the formula is doing.
    B2 = Create Date
    C2 = Resolved Date
    5PM - 8 AM are the hours that need to be excluded because the SLA stops at 5 and starts up again at 8 AM. The business hours are 8 - 5 Monday - Friday. Therefore we would exclude the weekends as well.


    NETWORKDAYS(B2,C2,Holidays)-1)*("17:00"-"08:00")+IF(NETWORKDAYS(C2,C2,Holidays),MEDIAN(MOD(C2,1),"08:00","17:00"),"17:00")-MEDIAN(NETWORKDAYS(B2,B2,Holidays)*MOD(B2,1),"08:00","17:00")

  9. #9
    Excel 2010, Table, Defined Names
    Accounts for SLA priority, working hours, lunch break, Sat/Sun and holidays.
    http://58918aa17d4002474361-7ac2b7ee.../05_10_13.xlsx
    If you get *.zip, don't unzip, just rename *.xlsx

  10. #10
    In your formula what does the @prioritytxt sign mean in =SUMIFS(Table3[Priority],Table3[P_Txt],[@PriorityTxt]), is that some kind of name list reference, if so how did you create,

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •