Solved: Formatting Time Values using VBA

[Slicemahn]

Hey everyone!

I am having a bit of problem formatting a time expressed in hours and minutes to reflect properly. The value is 50:17, but when I use VBA to format as "HH:MM" the result is 2:17. I have tried using the [H]:mm as in the text format with no results. I use

value= Format(inCell,"[hh]:mm")


Any ideas of what I am overlooking?

[mdmackillop]

You need something like
Range("A1").NumberFormat = "[hh]:mm"

[Bob Phillips]

value= Application.Text(inCell,"[h]:mm")

[Slicemahn]

So here is my code attempting to use my UDF to provide a narrative for an Executive summary for occupancy levels in a call centre. I have tried using the suggestions from the vbaexp community, only to have VAL# errors returned.

Public Function occExtHour(inExtHr As Date) As String

Dim retStr As String

retStr = "4) "

If (inExtHr = 0) Then
retStr = retStr & "No extended hours used "
Else
retStr = retStr & Format(inExtHr, "HH:MM") & " extended hours filled"
End If

retStr = retStr & " to cover any intervals over forecast."

occExtHour = retStr
End Function

[Charlize]

So here is my code attempting to use my UDF to provide a narrative for an Executive summary for occupancy levels in a call centre. I have tried using the suggestions from the vbaexp community, only to have VAL# errors returned.

Public Function occExtHour(inExtHr As Date) As String
Dim retStr As String
retStr = "4) "
If (inExtHr = 0) Then
retStr = retStr & "No extended hours used "
Else
'retStr = retStr & Format(inExtHr, "HH:MM") & " extended hours filled"
retStr = retStr & Format(inExtHr, "hh:nn") & " extended hours filled"
End If
retStr = retStr & " to cover any intervals over forecast."
occExtHour = retStr
End Function

Try the red line instead of yours. The cell that you check is formatted as a time. Maybe this will work ?
- C1 = 2:15 (in formule bar : 2:15:00)
- In E1 I use your function occExtHour(C1)
- Result : 4) 02:15 extended hours filled to cover any intervals over forecast.

Charlize

[Bob Phillips]

Public Function occExtHour(inExtHr As Double) As String

Dim retStr As String

retStr = "4) "

If (inExtHr = 0) Then
retStr = retStr & "No extended hours used "
Else
retStr = retStr & Application.Text(inExtHr, "[h]:mm") & " extended hours filled"
End If

retStr = retStr & " to cover any intervals over forecast."

occExtHour = retStr
End Function