Critical (P1)30 Mins (BH) 120 Mins(NBH)
High (P2) 60 Mins (BH) 240 Mins(NBH)
Average (P3) 90(BH) 360 Mins(NBH)
Low(P4) 120 Mins (BH) 480 Mins(NBH)
Business Hours is 0800 hrs - 2000 hrs
In the attached excel sheet have to define column I as BH or NBH with reference as call open time and classify SLA breached as true or false according to priority and when the call was logged whetehr in BH or in NBH.
In Column I use
Please Login or Register to view this content.What priority and BH/NBH combination gives true and what gives false?and classify SLA breached as true or false according to priority and when the call was logged whetehr in BH or in NBH.
Life's a spreadsheet, Excel!
Say thanks, Click *
Hi Ace,
It works fantastic, now i stumbled upon a roadblock, when i extract and copy the values , what happens is that till 556 row the date is getting copied as dd/mm/yyyy format , but after that the data is copied as mm/dd/yyyy and the formula unable to display any value. Have attached the sample sheet , how to resolve this?
In a separate cell convert all such data by using
=DATE(MID(B553,7,4),MID(B553,4,2),LEFT(B553,2))+RIGHT(B553,8)*1
Then copy and paste special -- values on to original data
Re: Incident report SLA calculation w.r.t criticality and calculating business hours or no
Splendid...awesome thanks a ton...u have made it a lot easier. Would you care to please educate me on how u did it?
Still one more query of mine is that based on BH or NBH , is it possible to calculate whether a call has breached SLA (TRUE or FALSE)
For instance if a call is logged during business hours and has got a priority as P1 then the resolution time is 30 Mins, and if its during NBH then the resolution time is 60 Mins.
Excel reads these values as text. I have simply extracted the dates from the text string using MID and LEFT formulas and enclosing it within the DATE(year,month,day) formula and added the time (again extracted by using RIGHT function)
Use this formula to ascertain SLA breachStill one more query of mine is that based on BH or NBH , is it possible to calculate whether a call has breached SLA (TRUE or FALSE)
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks