Forum Discussion

Shrey24's avatar
Shrey24
Copper Contributor
Jan 04, 2020

How to change macro output from 12 hour to 24 hour format?

Im coding using VBA in excel... Everytime I use a Date variable to store a time value, the output is always in 12 hour clock format. I have used functions to change this but it doesnt work. How do I convert the msgbox output to 24 hour format?

excel
Formulas and Functions
Macros and VBA
office 365

5 Replies

Sort By
  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Jan 04, 2020

    Shrey24

     

    Hi,

     

    Instead of Date variable, use Variant alone with the https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/format-function-visual-basic-for-applications as follows:

    Sub Test()
      Dim MyDate As Variant
      MyDate = Format(Time, "hh:mm")
      MsgBox MyDate
    End Sub

     

    Or without any variables like this:

    MsgBox Format(Time, "hh:mm")

     

    If you insist to use Date variable, try this:

    Sub Test2()
      Dim MyDate As Date
      MyDate = Time
      MsgBox WorksheetFunction.Text(MyDate, "[hh]:mm:ss")
    End Sub

     

    Hope that helps

Resources