Format(Time(), "h:mm") gives wrong format

Hi,
I have a weird problem with time format in userform textbox. The code is simple:

UserForm1.TextBox1.Text = Format(Time(), "h:mm")

The code above should enter current time in h:mm format to a textbox. Yet it doesn't, as instead it enters the time in whatever format Windows is currently set to.
No other pieces of code affect any of this and the problem is certainly this little piece of simple code, which just doesn't do what it should. What's wrong?

Thanks

I'm seeing h.mm and I want to see h:mm

example
I want 15:32 and I get 15.32

Use command button and run this

Private Sub CommandButton1_Click()
UserForm1.TextBox1.Value = Format(Time, "hh:mm:ss AM/PM")
End Sub

I get 02.36.08 PM

Originally Posted by chandy123987

Use this time format

Private Sub CommandButton1_Click()
UserForm1.TextBox1.Value = Format(Time, "hh:mm")
End Sub

tried it earlier, no change

Have you tried

"hh:mm"

or

Format(now(),"h:mm")

Originally Posted by nigelog

Have you tried

"hh:mm"

or

Format(now(),"h:mm")

I have tried both. Same results in both

Update: it gets weirder

I tried typing a time into a field in format h:mm, then using Format(cell.value, "h:mm"). This also gives h.mm

THEN, as the cell text was already in the correct format, I tried just

UserForm1.TextBox1.Text = cell.value

now it gave me h,mm !

Note that all of this worked for me before with system format being h:mm but now I need it to be able to function with different system format

I

Originally Posted by Andy Pope

Use the following in the immediate window and post back the result.

?format(time(),"h:mm"),time

I don't understand what you want me to do. I tried but excel doesn't understand what i'm trying to do

Originally Posted by Roel Jongman

This wierd result is because for every date and time format there is 1 format that will adjust to local date and time settings.

if you want to avoid this conversion try using

UserForm1.TextBox1.Text = Format(Time(), "h:mm,@")

this will force the use of 9:00 notation

This just added an actual @ to the string, as in:

14.38,@

Originally Posted by Jupsu

This just added an actual @ to the string, as in:

14.38,@

Ah ok.. this is one of the few times you need semicolon as separator I think

try this

UserForm1.TextBox1.Text = Format(Time(), "h:mm;@")

Originally Posted by Roel Jongman

Ah ok.. this is one of the few times you need semicolon as separator I think

try this

UserForm1.TextBox1.Text = Format(Time(), "h:mm;@")

You were correct, it now shows only the time.
Bad news is, the time is still in format h.mm :D

Personally I would be doubting if I'm sure about nothing else affecting the result at this point. So just to assure you, I tried changing the whole thing to

UserForm1.TextBox1.Text = "debug"

and indeed, "debug" I got

It's your locale !!!

Originally Posted by torachan

It's your locale !!!

Indeed it is, as I purposely set is as such to test If my code can handle different locales. The idea is to force the file to use one format no matter what locale

Originally Posted by Olly

You could use:

UserForm1.TextBox1.Text = Hour(Time()) & ":" & Minute(Time())

I don't understand why I didn't think of that.
I'll have to give it a go tomorrow, but if even that will fail, then I don't trust anything anymore.
Thanks!

Originally Posted by Olly

You could use:

UserForm1.TextBox1.Text = Hour(Time()) & ":" & Minute(Time())

I haven't tested this yet but I realized that since a lot of code is based in these time values, this would probably complicate using this value a lot?
For example calculating total hours from values like this would have to be much more complicated formula, and others like it

Originally Posted by Andy Pope

or perhaps

format(cdbl(time()),"h:mm")

this didn't work either

I get a lot of good ideas, out of which none that I have tried have worked yet.
Does anyone understand WHY this happens?

Hi,

or from a string data you can use the VBA CDate function …