Format function doesn't recognise [hh]:mm
 

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

--
Message posted from http://www.ExcelForum.com

Format function doesn't recognise [hh]:mm
 

On Mon, 5 Apr 2004 08:32:07 -0500, jennie
wrote:

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


[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

Format function doesn't recognise [hh]:mm
 

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



"jennie " wrote in message
...
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


---
Message posted from http://www.ExcelForum.com/

Format function doesn't recognise [hh]:mm
 

Thank you for your help
I will go and try them!

Thanks again
Jenni

--
Message posted from http://www.ExcelForum.com