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,386

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,522
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
I dont' follow what you're asking. You already seem to have formulas to get that in the Violation column?
 

ethelk2044

New member
Joined
Mar 27, 2013
Messages
3
Reaction score
0
Points
0
Do you have a formula to exclude business hours. I want to exclude the hours from 5 PM - 8:00 AM.
 

Kevin@Radstock

New member
Joined
Oct 5, 2012
Messages
52
Reaction score
0
Points
0
Excel Version(s)
365
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
 

ethelk2044

New member
Joined
Mar 27, 2013
Messages
3
Reaction score
0
Points
0
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
 

Kevin@Radstock

New member
Joined
Oct 5, 2012
Messages
52
Reaction score
0
Points
0
Excel Version(s)
365
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
 

ethelk2044

New member
Joined
Mar 27, 2013
Messages
3
Reaction score
0
Points
0
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")
 

manke0730

New member
Joined
Dec 17, 2013
Messages
1
Reaction score
0
Points
0
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,
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
2010
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
 

Sara

New member
Joined
Mar 22, 2017
Messages
1
Reaction score
0
Points
0
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
 
Top