Macro for SLA Calculation and summarized reporting

[pns.nava]

Hi,

I am new to excel, and I want to calculate SLA using the Macro function.

I want calculate the SLA for Initial response sent and the resolution of the tickets which handled by team.

We don't have any specific tool to record the data, we are using excel to record all the data. and currently we are calculating using some formula... However we are not able to achieve the result 100%

Requirement as follows :-

1. We are getting the request through Email and the assigned to each team member.

2. We are calculating SLA based on Severity levels

3. Working hours is 09:00 AM to 06:00AM

4. Holidays, Saturdays and Sundays need to ignore while calculating SLA

5. Need to consider the Waiting period with user(if any)

6. In excel file, we have Inquiry date and time, Accepted date and time and Completion date and time (Note : Date and time in separate column.)

From the above information and criteria, I have to find out the

1) Due Date with time based on the severity level of the request,
2) Elapsed time for process (How many hours left to breach SLA) and
3) SLA Breached or Not. (both Initial Response time and Resolution time)
4) Color coding is also required (if possible) SLA Breach - RED.
5) Color Coding is required for point no2 Elasped time for Process
RED - Based on SLA time line (detailed information provided in attached file)

I have attached the excel contain the details, please check and help me to achieve it.

Thank you in advance for the support

Best Regards,
Sankar

[pns.nava]

Dear Experts,

Any suggestion on the above post

Plz help

[SHANMU509]

Hi All,

I am also in the need of similiar spreadsheet which calculate the SLA Violation... Please help. Thanks,


Regards,
Shanmugam Sama

[venkateshvj]

Hi All,

I am also in the need of similiar spreadsheet. Can someone provide us the sample template?


Regards,
Venkatesh