S
Sheela
Hello,
I'm trying to automate an excel spreadsheet to track the
employees' resolution time to problem solving.
I have a standard table where it states the time period
to be responded for a particular job. In this standard
table, I have created another column whereby I created a
job id for each of the task.
In another worksheet, I have created a table as below:
Date JobId TimeReceived TimeSolved TotalHours
SLACompliance
I have created the formula for TotalHours which
calcutates the time taken to complete a task.
The problem I'm facing now is to create a formula for SLA
Compliance. From the standard table, I have named the
cells which has the same respond time respectively. I
want to create a formula in VBA where each time when the
employee keys in the JobID referring to the standard
table, The SLACompliance should lookup the range where
the JobID falls in and calculates the time period which
he has to respond to. If he managed to solve within the
time frame, a message should appear like "you've done a
good job" otherwise if vice versa "not a good job".
I had roughly came with a coding in VBA but its not
complete:
---------------------
Public Function SLACompliance(JobID, TotalHours)
If JobID = "twoworkingday" Then
SLACompliance = VLookup(twoworkingday)
ElseIf JobID = "eighthours" Then
SLACompliance = WLookup(eighthours)
End If
End Function
Private Function VLookup(twoworkingday, eighthours)
twoworkingday = TotalHours < 48
eighthours = TotalHours < 8
End Function
I'm trying to automate an excel spreadsheet to track the
employees' resolution time to problem solving.
I have a standard table where it states the time period
to be responded for a particular job. In this standard
table, I have created another column whereby I created a
job id for each of the task.
In another worksheet, I have created a table as below:
Date JobId TimeReceived TimeSolved TotalHours
SLACompliance
I have created the formula for TotalHours which
calcutates the time taken to complete a task.
The problem I'm facing now is to create a formula for SLA
Compliance. From the standard table, I have named the
cells which has the same respond time respectively. I
want to create a formula in VBA where each time when the
employee keys in the JobID referring to the standard
table, The SLACompliance should lookup the range where
the JobID falls in and calculates the time period which
he has to respond to. If he managed to solve within the
time frame, a message should appear like "you've done a
good job" otherwise if vice versa "not a good job".
I had roughly came with a coding in VBA but its not
complete:
---------------------
Public Function SLACompliance(JobID, TotalHours)
If JobID = "twoworkingday" Then
SLACompliance = VLookup(twoworkingday)
ElseIf JobID = "eighthours" Then
SLACompliance = WLookup(eighthours)
End If
End Function
Private Function VLookup(twoworkingday, eighthours)
twoworkingday = TotalHours < 48
eighthours = TotalHours < 8
End Function