Consulting

Results 1 to 9 of 9

Thread: Problem with format "12/31/1899"

  1. #1

    Problem with format "12/31/1899"

    Hi,

    I need help with formatting the time in ActiveX textboxes . For example, when I enter "22:00" in textbox7, all the other textboxes start out as "12/31/1899". The time calculated is correct, I just want to get rid of the "12/31/1899".

    Looking all over online and can't find anything to fix it. Any help is appreciated!

    Thanks!

    Heres the code:

    Private Sub ToggleButton20_Click()
    If ToggleButton20.Value = True Then
    ToggleButton20.BackColor = vbGreen

    TextBox8.Value = TimeValue(TextBox7.Value) + TimeValue("3:25")
    TextBox9.Value = TimeValue(TextBox7.Value) + TimeValue("6:50")
    TextBox10.Value = TimeValue(TextBox7.Value) + TimeValue("10:15")
    TextBox11.Value = TimeValue(TextBox7.Value) + TimeValue("13:40")
    TextBox12.Value = TimeValue(TextBox7.Value) + TimeValue("17:05")
    TextBox13.Value = TimeValue(TextBox7.Value) + TimeValue("22:30")

    end if
    End sub

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,080
    Location
    Try this alternative

    TextBox8.Value = Application.Text(TimeValue(TextBox7.Value)+ TimeValue("3:25"),"[h]:mm")
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    xld,

    I tried the code, but it pops up with a compile error.

    thanks for th response tho.

  4. #4
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,651
    Location
    Textboxes contain strings so I think CDate is better

    I'd use TimeSerial to add 3 hours and 25 minutes (for example)

    Format the strings before you put them into the textboxes

    Something like this maybe

    Sub test()
        Dim TB7 As String, TB8 As String, TB9 As String
        
        TB7 = "22:00"
        TB8 = CDate(TB7) + TimeSerial(3, 25, 0)
        TB9 = CDate(TB8) + TimeSerial(6, 50, 0)
        
        TB7 = Format(TB7, "[h]h:mm")
        TB8 = Format(TB8, "[h]h:mm")
        TB9 = Format(TB9, "[h]h:mm")
        
        
        
        MsgBox TB7 & vbCrLf & TB8 & vbCrLf & TB9
    End Sub
    
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    Thanks for the help Paul! I played with the code and I only used the format function to get rid of the "12/31/1899". And it works!

    Thanks for all your help!

    Here is the code:

    TextBox8.Value = TimeValue(TextBox7.Value) + TimeValue("3:25")
    TextBox9.Value = TimeValue(TextBox7.Value) + TimeValue("6:50")


    TextBox8 = Format(TextBox8.Value, "[h]h:mm")
    TextBox9 = Format(TextBox9.Value, "[h]h:mm")

  6. #6
    VBAX Master
    Joined
    Feb 2007
    Posts
    1,864
    Location
    You could also try

    Private Sub ToggleButton20_Click()
    If ToggleButton20.Value = True Then
    ToggleButton20.BackColor = vbGreen
    
    
    TextBox8.Value = Format(TimeValue(TextBox7.Value) + TimeValue("3:25"), "hh:mm")
    TextBox9.Value = Format(TimeValue(TextBox7.Value) + TimeValue("6:50"), "hh:mm")
    'etc
    
    
    End If
    End Sub
    John Wilson
    Microsoft PowerPoint MVP
    Amazing Free PowerPoint Tutorials
    http://www.pptalchemy.co.uk/powerpoi...tutorials.html

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,080
    Location
    Quote Originally Posted by John Wilson View Post
    You could also try

    Private Sub ToggleButton20_Click()
    If ToggleButton20.Value = True Then
    ToggleButton20.BackColor = vbGreen
    
    TextBox8.Value = Format(TimeValue(TextBox7.Value) + TimeValue("3:25"), "hh:mm")
    TextBox9.Value = Format(TimeValue(TextBox7.Value) + TimeValue("6:50"), "hh:mm")
    'etc
    
    End If
    End Sub
    Problem with that is it does not show > 24 hours, 22:00 + 3:25 is 24:25, but that shows as 1:25.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,080
    Location
    Quote Originally Posted by Paul_Hossler View Post
    Format the strings before you put them into the textboxes
    Format(TB7, "[h]h:mm") shows it as 1:25 on my machine Paul, [h] is not valid in Format, that is why I used Application.Text.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  9. #9
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,651
    Location
    Using Win10 Pro, Office 365, 32 bit the only thing [h] seems to do is surpress a leading '0' in Hours

    Adding 'ampm' formats using 12 hour clock.

    1.JPG2.JPG3.JPG4.JPG
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Posting Permissions

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