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?
You need something like
[VBA]Range("A1").NumberFormat = "[hh]:mm"[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
[vba]
value= Application.Text(inCell,"[h]:mm")
[/vba]
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.
[VBA]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
[/VBA]
Try the red line instead of yours. The cell that you check is formatted as a time. Maybe this will work ?Originally Posted by Slicemahn
- 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
[vba]
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
[/vba]
Posting Permissions
Reply With Quote
