# Thread: Need a formula to prepare SLA report

1. ## Need a formula to prepare SLA report

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.

2. I dont' follow what you're asking. You already seem to have formulas to get that in the Violation column?

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

4. What about weekends and public holidays ? Shouldn't you exclude these as well?

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. ## 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.

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. 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. 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. 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. 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 Last

#### Posting Permissions

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