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

**Jupsu** · 11-26-2019, 08:06 AM

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

Please Login or Register  to view this content.

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

**Olly** · 11-26-2019, 08:21 AM

I don't understand - what format are you seeing, and what are you expecting?

**chandy123987** · 11-26-2019, 08:24 AM

Use command button and run this

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

**nigelog** · 11-26-2019, 08:26 AM

Have you tried

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

**Jupsu** · 11-26-2019, 08:28 AM

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

Please Login or Register  to view this content.

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

**Andy Pope** · 11-26-2019, 08:28 AM

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

Please Login or Register  to view this content.

**Jupsu** · 11-26-2019, 08:30 AM

Originally Posted by nigelog

Have you tried

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

I have tried both. Same results in both

**Roel Jongman** · 11-26-2019, 08:30 AM

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

if you want to avoid this conversion try using

Please Login or Register  to view this content.

this will force the use of 9:00 notation

**Jupsu** · 11-26-2019, 08:34 AM

I

Originally Posted by Andy Pope

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

Please Login or Register  to view this content.

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

**Jupsu** · 11-26-2019, 08:35 AM

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

example
I want 15:32 and I get 15.32

**Jupsu** · 11-26-2019, 08:36 AM

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

**Jupsu** · 11-26-2019, 08:39 AM

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

Please Login or Register  to view this content.

this will force the use of 9:00 notation

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

14.38,@

**torachan** · 11-26-2019, 08:39 AM

It's your locale !!!

**Roel Jongman** · 11-26-2019, 08:44 AM

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

Please Login or Register  to view this content.

**Jupsu** · 11-26-2019, 08:45 AM

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

**Jupsu** · 11-26-2019, 08:48 AM

Originally Posted by Roel Jongman

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

try this

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

and indeed, "debug" I got

**Olly** · 11-26-2019, 08:51 AM

You could use:

Please Login or Register  to view this content.

**Jupsu** · 11-26-2019, 08:53 AM

Originally Posted by Olly

You could use:

Please Login or Register  to view this content.

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!

**Andy Pope** · 11-26-2019, 08:57 AM

or perhaps

Please Login or Register  to view this content.

**Jupsu** · 11-27-2019, 03:05 AM

Originally Posted by Olly

You could use:

Please Login or Register  to view this content.

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

**Jupsu** · 11-27-2019, 03:05 AM

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

**chandy123987** · 11-27-2019, 05:36 AM

Use this time format

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

**Olly** · 11-27-2019, 06:04 AM

Originally Posted by Jupsu

For example calculating total hours from values like this would have to be much more complicated formula, and others like it

Your purpose is not clear. You're trying to store a time value as a string in a textbox, then you want to perform time calculations on this value? Using TIMEVALUE on the textbox value (formatted per my suggestion) should work:

Please Login or Register  to view this content.

**Marc L** · 11-27-2019, 07:32 AM

Hi,

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

**Jupsu** · 11-28-2019, 07:35 AM

Originally Posted by Andy Pope

or perhaps

Please Login or Register  to view this content.

this didn't work either

**Jupsu** · 11-28-2019, 07:35 AM

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

**Andy Pope** · 11-29-2019, 04:41 AM

If the time is give you a decimal point rather than colon then how about a simple text replace?

Please Login or Register  to view this content.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Converting "hh h mm m ss s" time format to "hh:mm:ss"

[SOLVED] Remove "Time" from Date/Time format when importing from MS Query

[SOLVED] Excel 2007 : Converting "mm min ss s" time format to "hh:mm:ss"

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions