Userform formats time incorrectly
 

Would someone help me understand this, please? In my user form, only
particular numbers format incorrectly into time with the following
code. (This is much simplified from the original, but still produces
the same problem.)

Private Sub UserForm_Initialize()

'This actually pulls in a variable from a cell, but shown with a
constant here.
TextBox1.Value = 0.25

'Later in the code, this formats the textbox to display time.
TextBox1.Value = Format(TextBox1.Value, "h:mm")

End Sub

It works with every number but .25 (6am) and .5 (noon). They format
incorrectly as "0:25" (12:25 am) and "0.05" (12:05 am), respectively.
All other numbers work, but I need it to work for these times as well.

???

Thanks,
Chuck

Userform formats time incorrectly
 

Chuck,
Coerce the value to a single before the formatting:

TextBox1.Value = Format(CSng(TextBox1.Value), "h:mm")

NickHK

"c mateland" wrote in message
oups.com...
Would someone help me understand this, please? In my user form, only
particular numbers format incorrectly into time with the following
code. (This is much simplified from the original, but still produces
the same problem.)

Private Sub UserForm_Initialize()

'This actually pulls in a variable from a cell, but shown with a
constant here.
TextBox1.Value = 0.25

'Later in the code, this formats the textbox to display time.
TextBox1.Value = Format(TextBox1.Value, "h:mm")

End Sub

It works with every number but .25 (6am) and .5 (noon). They format
incorrectly as "0:25" (12:25 am) and "0.05" (12:05 am), respectively.
All other numbers work, but I need it to work for these times as well.

???

Thanks,
Chuck

Userform formats time incorrectly
 

Thanks, Nick. I'll give it a try.

I still don't understand some things, however. If not already a single
data type, what was it? Why did it function correctly for all serial
numbers except .25 and .5? Why does it function correctly if I
reference the cell value directly in the format command:

TextBox1.Value = Format(range("a1").value, "h:mm")

Just trying to understand so I know WHEN to do this and when I don't
need to.

Thanks,
Chuck


NickHK wrote:
Chuck,
Coerce the value to a single before the formatting:

TextBox1.Value = Format(CSng(TextBox1.Value), "h:mm")

NickHK

"c mateland" wrote in message
oups.com...
Would someone help me understand this, please? In my user form, only
particular numbers format incorrectly into time with the following
code. (This is much simplified from the original, but still produces
the same problem.)

Private Sub UserForm_Initialize()

'This actually pulls in a variable from a cell, but shown with a
constant here.
TextBox1.Value = 0.25

'Later in the code, this formats the textbox to display time.
TextBox1.Value = Format(TextBox1.Value, "h:mm")

End Sub

It works with every number but .25 (6am) and .5 (noon). They format
incorrectly as "0:25" (12:25 am) and "0.05" (12:05 am), respectively.
All other numbers work, but I need it to work for these times as well.

???

Thanks,
Chuck

Userform formats time incorrectly
 

I can't repeat you results. From the immediate window, I get:

?format("0.1", "h:mm")
0:01
?format(0.1, "h:mm")
2:24
?format("0.5", "h:mm")
0:05
?format(0.5, "h:mm")
12:00
?format("1", "h:mm")
0:00
?format(1, "h:mm")
0:00

And the Range.Value is a Variant, so it's sub-type could numeric, in which
case Format to time would be correct.
However, TextBox.Value is always a String, so you have to rely on VBA
guessing what should happen correctly, which (apparently) it does not.
The CSng() ensures the correct data type is used for the formatting.

NickHK

"c mateland" wrote in message
ups.com...
Thanks, Nick. I'll give it a try.

I still don't understand some things, however. If not already a single
data type, what was it? Why did it function correctly for all serial
numbers except .25 and .5? Why does it function correctly if I
reference the cell value directly in the format command:

TextBox1.Value = Format(range("a1").value, "h:mm")

Just trying to understand so I know WHEN to do this and when I don't
need to.

Thanks,
Chuck


NickHK wrote:
Chuck,
Coerce the value to a single before the formatting:

TextBox1.Value = Format(CSng(TextBox1.Value), "h:mm")

NickHK

"c mateland" wrote in message
oups.com...
Would someone help me understand this, please? In my user form, only
particular numbers format incorrectly into time with the following
code. (This is much simplified from the original, but still produces
the same problem.)

Private Sub UserForm_Initialize()

'This actually pulls in a variable from a cell, but shown with a
constant here.
TextBox1.Value = 0.25

'Later in the code, this formats the textbox to display time.
TextBox1.Value = Format(TextBox1.Value, "h:mm")

End Sub

It works with every number but .25 (6am) and .5 (noon). They format
incorrectly as "0:25" (12:25 am) and "0.05" (12:05 am), respectively.
All other numbers work, but I need it to work for these times as well.

???

Thanks,
Chuck