Format function doesn't recognise [hh]:mm

  • Thread starter Thread starter jennie
  • Start date Start date
J

jennie

Hi,

Is there a way of getting the format function in VBA or anothe
function that will do the same job to recognise the custom forma
[hh]:mm?

When reopening a user form, it fills in the values from the relevan
line on the worksheet into the boxes.
However as the time in the worksheet can be over 24 hours, e.g. 4
hours (by using the custom format [hh]:mm), I would like the box i
the user form to show the value in hours and minutes too even if it i
above 23:59:59.

This does not work
cboResponse1.Value = Format (ActiveCell.Offset(0, 8).Value, "[hh]:mm")


This only works for values under 24:00
cboResponse1.Value = Format (ActiveCell.Offset(0, 8).Value, "hh:mm")


cboResponse1.Value = ActiveCell.Offset(0, 8).Value
and this return just displays the time in a numerical format e.g
1.45.....


Thanks for any help you can give
Jenni
 
Hi,

Is there a way of getting the format function in VBA or another
function that will do the same job to recognise the custom format
[hh]:mm?

When reopening a user form, it fills in the values from the relevant
line on the worksheet into the boxes.
However as the time in the worksheet can be over 24 hours, e.g. 48
hours (by using the custom format [hh]:mm), I would like the box in
the user form to show the value in hours and minutes too even if it is
above 23:59:59.

This does not work
cboResponse1.Value = Format (ActiveCell.Offset(0, 8).Value, "[hh]:mm")


This only works for values under 24:00
cboResponse1.Value = Format (ActiveCell.Offset(0, 8).Value, "hh:mm")


cboResponse1.Value = ActiveCell.Offset(0, 8).Value
and this return just displays the time in a numerical format e.g.
1.45.....


Thanks for any help you can give
Jennie
Click to expand...


[h] is NOT a listed user-defined format string for the VBA Format function.

I believe you could use the TEXT worksheet function, though:

cboResponse1.Value = worksheetfunction.text _
(ActiveCell.Offset(0, 8).Value,"[hh]:mm")


--ron
 
jennie,

Failing something more elegant, you could 'force' it with something like
this:

Sub Tester()
cboResponse1.Value = fTimeText(ActiveCell.Offset(0, 8).Text)
End Sub
Function fTimeText(stest)
If stest <> Empty Then
ColonPos = InStr(1, stest, ":")
H = Left(stest, ColonPos - 1)
M = Right(stest, Len(stest) - ColonPos)
fTimeText = H & ":" & M
End If
End Function

HTH,
Shockley
 
Back
Top