• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need a formula to prepare SLA report

Gizmotravis

New Member
Hi

I need a formula that works out the time from when a case was opened to when it is closed...the SLA's are as follows
High= 22:30 hours
Medium= 37:30 hours
Low=75 hours

But here is the challenge...It needs to only take into consideration Business hours
Business hours are M - F 8:30 - 17:30
Example
Priority Case Open Case Close SLA Hrs Actual Hrs SLA
High Thu 19/01/12 12:52 Tue 24/01/12 09:50 22:30 22:28 Within SLA
High Wed 02/22/12 00:29 Tue 02/28/12 00:29 22:30 34:00 Exceeds SLA

If the ticket is closed within the SLA then the result should be “Within SLA” else “Exceeding SLA”

Hope someone can help

Thxs
 
Sorry..first time poster long time lurker...here is a sample of the data and the Actual AHL is what I am trying to calculate. Hope you can help
 

Attachments

  • Case Management SLA.xlsx
    14.7 KB · Views: 25
Hi:

Formula in Yellow Cells.

Thanks
 

Attachments

  • Case Management SLA.xlsx
    13.7 KB · Views: 41
Hi Nebu
Thank you so much...your help is greatly appreciated
Just a quick question the result on the first line should be 1:11 not 1:18?
am I missing something
 
Hi:

Yes, when you convert the time into decimal 1:11 will become 1.18 , you can cross check this by formatting the cell with custom format hh:mm.

Thanks
 
Hi Nebu

Really sorry about this but if i try and change the fomat it makes the time 04:24??? Can you update the attached doc so I can see what you mean please? I really do appreciate your time and help on this

Thxs
 
Hi Nebu

Dont worry I think I have resolved it using your concept...so, if i use the following it seems to work IF(0.354166666666667*(NETWORKDAYS(D3,E3)-1)=0,0,0.354166666666667*(NETWORKDAYS(D3,E3)-1))+(TEXT(E3,"hh:mm")-TEXT(D3,"hh:mm"))

Thxs for your help
 
Back
Top