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

%3CLINGO-SUB%20id%3D%22lingo-sub-1090869%22%20slang%3D%22en-US%22%3EHow%20to%20change%20macro%20output%20from%2012%20hour%20to%2024%20hour%20format%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1090869%22%20slang%3D%22en-US%22%3E%3CP%3EIm%20coding%20using%20VBA%20in%20excel...%20Everytime%20I%20use%20a%20Date%20variable%20to%20store%20a%20time%20value%2C%20the%20output%20is%20always%20in%2012%20hour%20clock%20format.%20I%20have%20used%20functions%20to%20change%20this%20but%20it%20doesnt%20work.%20How%20do%20I%20convert%20the%20msgbox%20output%20to%2024%20hour%20format%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1090869%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1090918%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20change%20macro%20output%20from%2012%20hour%20to%2024%20hour%20format%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1090918%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F506325%22%20target%3D%22_blank%22%3E%40Shrey24%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EInstead%20of%20Date%20variable%2C%20use%20Variant%20alone%20with%20the%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Fvba%2Flanguage%2Freference%2Fuser-interface-help%2Fformat-function-visual-basic-for-applications%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EFormat%20function%3C%2FA%3E%20as%20follows%3A%3C%2FP%3E%3CPRE%3ESub%20Test()%3CBR%20%2F%3E%20%20Dim%20MyDate%20As%20Variant%3CBR%20%2F%3E%20%20MyDate%20%3D%20Format(Time%2C%20%22hh%3Amm%22)%3CBR%20%2F%3E%20%20MsgBox%20MyDate%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOr%20without%20any%20variables%20like%20this%3A%3C%2FP%3E%3CPRE%3EMsgBox%20Format(Time%2C%20%22hh%3Amm%22)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EIf%20you%20insist%20to%20use%3C%2FSPAN%3E%3C%2FSPAN%3E%20Date%20variable%2C%20try%20this%3A%3C%2FP%3E%3CPRE%3ESub%20Test2()%3CBR%20%2F%3E%20%20Dim%20MyDate%20As%20Date%3CBR%20%2F%3E%20%20MyDate%20%3D%20Time%3CBR%20%2F%3E%20%20MsgBox%20WorksheetFunction.Text(MyDate%2C%20%22%5Bhh%5D%3Amm%3Ass%22)%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1091104%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20change%20macro%20output%20from%2012%20hour%20to%2024%20hour%20format%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1091104%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3EThanks%20a%20lot!%20I%20have%20been%20struggling%20for%20quite%20a%20long%20time%20and%20I%20think%20I%20have%20finally%20gotten%20it%20after%20using%20the%20variable%20as%20a%20Variant.%20Thanks%20again.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1616203%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20change%20macro%20output%20from%2012%20hour%20to%2024%20hour%20format%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1616203%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3BI%20copied%20you%20%22Sub%20Test()%22%20program%20and%20run%20on%20my%20Excel%202016%2C%20but%20it%20still%20show%2012%20Hour%20format.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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?

3 Replies
Highlighted

@Shrey24

 

Hi,

 

Instead of Date variable, use Variant alone with the Format function 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

Highlighted
@Haytham Amairah 
Thanks a lot! I have been struggling for quite a long time and I think I have finally gotten it after using the variable as a Variant. Thanks again.
Highlighted

@Haytham Amairah I copied you "Sub Test()" program and run on my Excel 2016, but it still show 12 Hour format.