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 need some help on this.

Thanks.

Public Function SLACompliance(JobID, TotalHours)
SLACompliance = Vlookup(JobID, TotalHours)
End Function

Private Function VLookup(JobId, TotalHours)
SLAComp = "No"
If JobID = "twoworkingday" Then
if TotalHours < 48 then
SLAComp= "Yes
End if
Elseif TotalHours < 8 then
SLAComp = "Yes"
End If
Vlookup = SLACompliance
End Function

Would be a guess at what you want.

Not sure how Twoworkingday would be 48 hours unless you work around the
clock.

--
Regards,
Tom Ogilvy

"Sheela" wrote in message
...
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 need some help on this.

Thanks.