SLA - Calculation

[suresh2017]

Hi All,

Please help me i am new to Excel i wanted to calculate SLA's based on the attached file.

-Suresh

 

[suresh2017]

Hi All,

Please help me i am new to Excel i wanted to calculate SLA's based on the attached file.

-Suresh

Ninjas ~ Please help me, Please read the SAL SLA Calculation.

Suresh2017

 

[NARAYANK991]

Hi ,

Please re-upload your workbook , after inserting the output that you expect , manually working out at least 4 or 5 values.

Ensure that the sample data you have inserted in the workbook covers all possible variations.

Help others so that they can help you.

Narayan

 

[suresh2017]

Hi ,

Please re-upload your workbook , after inserting the output that you expect , manually working out at least 4 or 5 values.

Ensure that the sample data you have inserted in the workbook covers all possible variations.

Help others so that they can help you.

Narayan

Hi All,


Based on request I have updated my Workbook and attached for solution.


Raw_Data_TAB


While calculating D Column (Date Closed - Date Opened)

I need to exclude Holidays – TAB Holidays days and

Working_Hours – TAB - Weekends (Saturday & Sunday) and

Work Hours 9:00 AM to 6:00 PM – Only 8 Hours Per day (1 Hour is Lunch).


Based on D Column Hours below SLA’s I need to display E Column

"Within SLA" or "Exceeding SLA" or If Close Date is Empty "NOT CLOSED"

Severity

Target Resolution Time

1: Immediate Impact

2 Hours and 5 Minutes

2: High Impact

6 Hours and 5 Minutes

3: Moderate Impact

3 Days and 2 Hours

4: Minor Impact

6 Days and 2 Hours

5: Question

1 Day and 2 Hours


F Column (for example 1: Immediate Impact Severity per month only 2 allowed if it repeats more than 2 times in month Exceeding SLA)


Severity

Maximum Number of Occurrence Per Month

1: Immediate Impact

Maximum 2 Per Month

2: High Impact

Maximum 4 Per Month

3: Moderate Impact

Maximum 8 Per Month

4: Minor Impact

Maximum 12 Per Month

5: Question

Maximum 16 Per Month

 

[NARAYANK991]

Hi ,

You have uploaded a workbook which has data , but what is the expected output ?

If you can fill up at least 4 or 5 output cells with the expected output , we will get a clearer idea of how the calculation should be done ; based on this , a formula can be suggested.

Narayan

 

[PaulF]

You have lots of work ahead. I used to be part of a group that had oversight on 110+ Desktop and Infrastructure techs working across AMER and UK regions. Your ask is more than a question, you are seeking a large solution. Start with smaller parts, getting raw data without all of the added text of days and minutes, create some tables to define to sla parameters, and calendar for schedules, holidays, and weekends. You will end up with a bunch of tables for support, calculations, and helper (tables and columns). Here was the solution I had in place. I learned all I needed with Chandoo's course a few years back. http://chandoo.org/wp/resources/learn-power-pivot/
I then had specific questions and got some guidance here in the forums along the way.