Excel 2007 : Need help in setting SLA bench mark

**sudarshan3677** · 02-26-2010, 01:06 AM

Hi,
I work in the IT service desk of a Software firm
Our work time is 9.30 am to 6.30 PM
Off days are Saturday and Sunday and holidays.

We have a tool that generates the Service desk tickets.
I need your help in calculating teh SLA bench mark so that i can get teh Achieved SLA in the excel.
Here is teh Defined SLA for resolution time.
Severity 1= 8 hours
Severity 2=12 Hours (8 hours+next day 4 hours)
Severity 3= 16 hours (2 business days of 8 hours each)
Severity 4= 24 hours (3 business day)

I have the ticket created time and ticket completed time. Im able to find the total time consumed per ticket. Based on the above bench SLA bench mark, I need to calculate teh total time elapsed against the SLA and with Percentage if achievement per ticket.

Please help. any of you may have already defined this calculation. if you have already done, kindly share the link if it is available in the forum.

Best regards
Sudarshan.

**DonkeyOte** · 02-26-2010, 03:46 AM

Though posted on a different forum the question is virtually identical - given complexity simplest to link:

http://www.mrexcel.com/forum/showpos...95&postcount=7

Working sample attached.

(file reloaded on 4th March - Col D formula missing $ which could have lead to error)