Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time Format Problem | New Users to Excel | |||
format time problem | Excel Discussion (Misc queries) | |||
time format problem | Excel Worksheet Functions | |||
Time format problem | Excel Discussion (Misc queries) | |||
Time format problem | Excel Programming |