+ Reply to Thread
Results 1 to 27 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 Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    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
    106

    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
    Office 365 Windows 10
    Posts
    2,286

    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
    O365
    Posts
    20,419

    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
    Office 365
    Posts
    1,482

    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
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    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
    Office 365
    Posts
    1,482

    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

  16. #16
    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
    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

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

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

    You could use:
    Please Login or Register  to view this content.

  18. #18
    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 Olly View Post
    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!

  19. #19
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

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

    or perhaps
    Please Login or Register  to view this content.

  20. #20
    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 Olly View Post
    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

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

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

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

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

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

    Use this time format

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

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

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

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

  24. #24
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Lightbulb

    Hi,

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

  25. #25
    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 Andy Pope View Post
    or perhaps
    Please Login or Register  to view this content.
    this didn't work either

  26. #26
    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 chandy123987 View Post
    Use this time format

    Private Sub CommandButton1_Click()
    UserForm1.TextBox1.Value = Format(Time, "hh:mm")
    End Sub
    tried it earlier, no change

  27. #27
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

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

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 07-13-2018, 11:52 PM
  2. [SOLVED] Converting "hh h mm m ss s" time format to "hh:mm:ss"
    By sohel.engr in forum Excel General
    Replies: 10
    Last Post: 09-28-2013, 03:15 PM
  3. [SOLVED] Remove "Time" from Date/Time format when importing from MS Query
    By auslegung in forum Excel General
    Replies: 5
    Last Post: 04-03-2013, 03:26 PM
  4. Replies: 2
    Last Post: 11-01-2012, 04:35 PM
  5. [SOLVED] Excel 2007 : Converting "mm min ss s" time format to "hh:mm:ss"
    By blackmilk in forum Excel General
    Replies: 15
    Last Post: 04-23-2012, 04:14 PM
  6. Replies: 4
    Last Post: 07-14-2010, 03:17 PM
  7. Extract "Time" from custom "Date and Time" format
    By ben_patto in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-24-2009, 08:47 PM

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