Problem with time format
Times are stored in units of days. 1 hour = 1/24.
On Tue, 19 Oct 2004 12:03:05 -0700, "JonR"
wrote:
Hello,
I have a VBA function that will populate cells according to the amount of
time allotted to fix a certain problem. It then compares this value against
the actual time it took the person to fix the problem.
My issue is that I populate the cells with what amounts to be an integer
value, but this doesn't work correctly wehn compared to the time to repair.
How do I correctly format both the time to repair and the standard value so
that the formats match and the two values get compared correctly?
Code follows
SLA is the standard allotted time
Cell values are calculated to reflect the time to repair
Comp is a boolean (Y,N) describing if the person met the standard
Sub compliance()
Dim inX As Integer
Dim stY As String
Dim SLA As Variant
Dim Comp As String
Dim current As Variant
'Application.ScreenUpdating = False
current = Format(Date + Time, "#####.#####")
'MsgBox current ' for Auditing
Worksheets("Peregrine Data").Visible = True
Worksheets("Peregrine Data").Activate
'Range("M2", Cells.End(xlDown)).Select
'Range("M2", Cells.End(xlDown)).Clear
'Range("N2", Cells.End(xlDown)).Select
'Range("N2", Cells.End(xlDown)).Clear
inX = Range(Cells(2, 1), Cells.End(xlDown)).Rows.Count
'MsgBox inX ' for auditing
For n = 2 To inX + 1
inY = Cells(n, 7).Value
Select Case inY
Case "P1/S1"
SLA = Format(1, "[h]:mm:ss")
Case "P1/S2"
SLA = Format(2, "[h]:mm:ss")
Case "P1/S3"
SLA = Format(5, "[h]:mm:ss")
Case "P2"
SLA = Format(24, "[h]:mm:ss")
Case "P3"
SLA = Format(72, "[h]:mm:ss")
Case "P4"
SLA = Format(120, "[h]:mm:ss")
End Select
Cells(n, 13).Value = SLA
If Cells(n, 8).Value < "" Then
Cells(n, 12).Value = Cells(n, 8).Value / 60
Else
Cells(n, 12).Value = "XXX"
'Cells(n, 12).Value = Format(DateDiff("[h]:mm:ss", current, Cells(n,
3).Value), "Long Time")
End If
If Cells(n, 12).Value <= SLA Then
Comp = "Y"
Else
Comp = "N"
End If
Cells(n, 14).Value = Comp
'
'Calculates Ack Time (Column O)
'
If Cells(n, 10) < "" Then
Cells(n, 15).Value = CDate(Cells(n, 10).Value) - CDate(Cells(n, 3).Value)
Cells(n, 15).NumberFormat = "[h]:mm:ss"
Else
Cells(n, 15).Value = ""
End If
Next n
'Worksheets("Peregrine Data").Visible = False
Application.ScreenUpdating = True
End Sub
|