VBA criteria to determine service level.

[magges]

Hi guys,

I could really need some help on setting up some VBA criteria.
Im doing an overview of how effecient we are at responding to clients. The data i have can be seen below. Title in first row and explanation in second.

Opened	Time opened	Created	Reaction Time	Resolved	Resolution time	Period	Made SLA
recieved from costumer​	Time used to determine period.​	Created as an action​	Difference between "Created" and "Opened" in minutes	date and time for closing​	Diffrence between"Resolved" and "Opened" in minutes	Period depending on time of day	TRUE or False
2019-11-01 19:02:00​	7:02:00 PM​	2019-11-01 19:02:00​	00	2020-02-03 14:25:33​	135083	Period 2	False​
2019-11-25 11:36:26​	3:30:00 AM​	2019-11-25 15:45:26​	249	2020-02-04 14:25:34​	102409	Period 1	TRUE​

The criteria for reaching the SLA (Service level agreement) can be seen below.

So i managed to make an IF statement including AND and OR to determine the time-period. After determining the period i would like to use reaction- and resolution minutes to determine if the request fulfilled the criteria according to time-period and priority. It will however be some very long function statements, so i was wondering if it was possible to setup as sub in VBA instead like the example below. The escalation requirement is not yet relevant btw.

Sub Criteria()
Dim wsh As Worksheet, i As Long
Set wsh = ActiveSheet

i = 2

If Cells(i, "K").Value < "P1 - Critical" And _ 
   Cells(i, "I").Value < "Period 1" And _
   Cells(i, "E").Value < "60" And _
   Cells(i, "G").Value < "180" Then
   Cells(i, "J").Value = "TRUE"

End If
    i = i + 1

End Sub

I could really use some help with the general setup and then the real tricky requirement which is period 2, P3-Minor, where it needs to be before 9 the following morning to comply with the service level agreement.

 

[Rijnsent]

Hi Magges,

I would do a couple of things: firstly, get your data in a table like this, so it's easier to check all the possibilities as you can loop through them:

After that, create a UDF (user defined function), see e.g. Free VBA Training Course -> lesson 6. This is a basic version without any input checks, put it in a Module, save the file and you can use is as a function in the sheet (or in another macro):

Function SLA_REACHED(TimeOpened As Date, Priority As String, ReactionTime As Double, ResolutionTime As Double) As Boolean

SLAdata = Worksheets("Sheet1").Range("A3:E10").Value

'Default output
SLA_REACHED = False

If TimeOpened <= #6:00:00 PM# And TimeOpened >= #6:00:00 AM# Then
    Period = "Period 1"
Else
    Period = "Period 2"
End If

ReactT = 0
ResT = 0
For i = 1 To UBound(SLAdata)
    If SLAdata(i, 1) = Period And SLAdata(i, 2) = Priority Then
        ReactT = SLAdata(i, 3)
        ResT = SLAdata(i, 5)
        Exit For
    End If
Next i

If ReactionTime <= ReactT And ResolutionTime <= ResT Then
    SLA_REACHED = True
End If

End Function

Does that make sense?

 

[magges]

Thank you very much Rijnsent! Works well