Need a formula to prepare SLA report

salafichennai

New member
Joined
Oct 25, 2012
Messages
2
Reaction score
0
Points
0
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.
 

Attachments

  • SLA Performance Report.xlsx
    31.5 KB · Views: 6,388
I dont' follow what you're asking. You already seem to have formulas to get that in the Violation column?
 
Do you have a formula to exclude business hours. I want to exclude the hours from 5 PM - 8:00 AM.
 
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
 
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
 
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
 
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")
 
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,
 
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,
Im not an expert on tables, but the names in the square brackets are placed in the header row of the table, and the column in which they appear can be referenced by that name. It works a bit like Named Ranges, but its
quicker and easier. If you want more, read up on Excel tables.

HTH
 
Hi Can you help me to create formula for the following details because i have different type of SLA measurements & working hours


SLA
Critical = 4 hours (calendar day)
High=8 hours (calendar day)
Medium= 9 hours (Business Hour)
Low= 18 hours (Business Hour)


Working Hours
Monday - Thursday : 8.45am - 5.45pm
Friday : 8.45am - 4.45pm
 
Back
Top