+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 27

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

  1. #1
    Registered User
    Join Date
    11-14-2019
    Location
    Finland
    MS-Off Ver
    2016
    Posts
    32

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

    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

  2. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,217

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

    I don't understand - what format are you seeing, and what are you expecting?
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Forum Contributor
    Join Date
    12-22-2017
    Location
    INDIA
    MS-Off Ver
    EXCEL 2007
    Posts
    104

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

    Use command button and run this

    Private Sub CommandButton1_Click()
    UserForm1.TextBox1.Value = Format(Time, "hh:mm:ss AM/PM")
    End Sub
    If solved press * add reputation

  4. #4
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    excel 2016 64bit - Windows 10
    Posts
    2,002

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

    Have you tried
    Please Login or Register  to view this content.
    or
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    11-14-2019
    Location
    Finland
    MS-Off Ver
    2016
    Posts
    32

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

    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

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    2003, 2007, 2010 & 2013
    Posts
    18,906

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

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

    Please Login or Register  to view this content.
    Cheers
    Andy
    www.andypope.info

  7. #7
    Registered User
    Join Date
    11-14-2019
    Location
    Finland
    MS-Off Ver
    2016
    Posts
    32

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

    Quote Originally Posted by nigelog View Post
    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

  8. #8
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    2016
    Posts
    1,335

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

    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
    Last edited by Roel Jongman; 11-26-2019 at 08:40 AM.

  9. #9
    Registered User
    Join Date
    11-14-2019
    Location
    Finland
    MS-Off Ver
    2016
    Posts
    32

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

    I
    Quote Originally Posted by Andy Pope View Post
    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

  10. #10
    Registered User
    Join Date
    11-14-2019
    Location
    Finland
    MS-Off Ver
    2016
    Posts
    32

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

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

    example
    I want 15:32 and I get 15.32

  11. #11
    Registered User
    Join Date
    11-14-2019
    Location
    Finland
    MS-Off Ver
    2016
    Posts
    32

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

    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

  12. #12
    Registered User
    Join Date
    11-14-2019
    Location
    Finland
    MS-Off Ver
    2016
    Posts
    32

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

    Quote Originally Posted by Roel Jongman View Post
    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,@

  13. #13
    Valued Forum Contributor
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    810

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

    It's your locale !!!

  14. #14
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    2016
    Posts
    1,335

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

    Quote Originally Posted by Jupsu View Post
    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.

  15. #15
    Registered User
    Join Date
    11-14-2019
    Location
    Finland
    MS-Off Ver
    2016
    Posts
    32

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

    Quote Originally Posted by torachan View Post
    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

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1