How to calculate incident SLA expiration

[Puni19]

Hi,

I work on a helpdesk and need to calculate when an incident SLA is due to expire, relative to the hours of contracted service. We provide service 0700 to 2300hrs 7 days a week, and are required to fix a fault within 85 minutes, I can add 85 minutes to an incident start time when the start time is well within the hours of service, however, am coming unstuck if the incident is created at say, 2230hrs. How do I calculate 30mins up to 2300hrs and then another apply the remaining 55 minutes from 0700hrs to show the SLA expires at 0755hrs?


Thanks,

 

[ask2tsp]

Please See https://www.dropbox.com/s/zbs110pswxnc2uq/SLAcalc.xlsx?dl=0

if the incident end time (incident start+SLA max time) falls within the service window all is fine;if not the 'out of service' time is added to the inc.end modulo 24h

hope you like it.

 

[Puni19]

Thanks Ask2tsp,

Ideally am looking for a formula I can drag through each row, have hundreds of incidents that I need to calculate. Any idea if the incident start date and time was in H2?

Thanks,

 

[Peter_SSs]

Please See https://www.dropbox.com/s/zbs110pswxnc2uq/SLAcalc.xlsx?dl=0

if the incident end time (incident start+SLA max time) falls within the service window all is fine;if not the 'out of service' time is added to the inc.end modulo 24h

hope you like it.

Please refer to #7 (final paragraph) of the Forum Rules

 

[ask2tsp]

@Peter_SSs:
I read the forum rules a couple of weeks ago and this one had slipped my mind. Sorry about that.
But I also learned that it is not possible to send attachments with a post. I tried to useMrExel HTML Maker but it failed; posted that in the About This Board forum; no replies yet.
Since I had already prepared a small worksheet demonstrating a possible answer to the stated question it then seemed a logical next step to present it.
---
@all:
For those unable or unwilling to load the workbook now follows a description of the method used.

slaEnd = IF( AND( iEnd <= stEnd; iEnd >= stStart ); iEnd; MOD( iEnd + outOfSrvPeriod; 1 ) )

Where:

stStart:  Start Servicetime
stEnd:      End   Servicetime
outOfSrvPeriod:   out of service period = 1 - ABS(stEnd  - stStart)
iEnd:     iStart +  slaMax
   iStart:      incident start time
   slaMax:   SLA agreed max. incident duration (in this case 1:25)

If you want one formula, so you can use it in many rows, it is best to build a function that you can use.
Every slaEnd cel has the formula

=slaEnd(adress of cel with iStart)

and a possible implementation of the slaEnd function could be:

Function slaEnd(iStart As Date) As Date
   Const stStart  As Date = #7:00:00 AM#
   Const stEnd    As Date = #11:00:00 PM#
   Const outOfSrvPeriod As Date = #8:00:00 AM#
   Const slaMax   As Date = #1:25:00 AM# 'max incident duration
   Dim iEnd As Date  'the incident end time
   
   iEnd = iStart + slaMax
   
   If iEnd < stStart Or iEnd > stEnd Then
      iEnd = iEnd + outOfSrvPeriod
      If iEnd > 1 Then iEnd = iEnd - 1
   End If
   slaEnd = iEnd
End Function

 

[Peter_SSs]

@Peter_SSs:
I tried to useMrExel HTML Maker but it failed; posted that in the About This Board forum; no replies yet.

Yes, I saw your post there but could really help. I have posted something there now which may possibly trigger more responses.