PDA

View Full Version : Solved: Formatting Time Values using VBA



Slicemahn
02-07-2007, 02:34 PM
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
02-07-2007, 03:35 PM
You need something like
Range("A1").NumberFormat = "[hh]:mm"

Bob Phillips
02-07-2007, 05:03 PM
value= Application.Text(inCell,"[h]:mm")

Slicemahn
02-08-2007, 12:27 AM
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
02-08-2007, 03:18 AM
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
02-08-2007, 04:13 AM
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