Problem with time format

[Guest]

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

 

[Tom Ogilvy]

VBA doesn't support the [h] format

Time is stored as a fraction of a 24 hour day, so 125 hours would be stored
as

5.20833333333333
for example:

hrs = (125\24) + (125 mod 24)/24
? hrs
5.20833333333333


Once you put that value in the cell, then do

ActiveCell.Numberformat = "[h]:mm:ss"

 

[Myrna Larson]

Times are stored in units of days. 1 hour = 1/24.