ExcelBanter - Problem with time format

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Problem with time format (https://www.excelbanter.com/excel-programming/314040-problem-time-format.html)

Problem with time format

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

Problem with time format

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"

--
Regards,
Tom Ogilvy

"JonR" wrote in message
...
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

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