This post will guide you how to convert military time to standard time with a formula in Excel. How do I convert from military time 17:29:30 to regular time 5:29:30PM in Excel. How do I Convert Standard time to military time with a formula in Excel.
1. Convert Military Time to Standard Time using Formula
Assuming that you have a list of data in range B1:B4, in which contain military time. And you wish to convert those times to standard time. How to do it.
You can a formula based on the TIMEVALUE function, the LEFT function, the MID function and the RIGHT function. The formula is similar to the below:
=TIMEVALUE(LEFT(B1,2)&":"&MID(B1,3,2)&":"&RIGHT(B1,2))Step1: You need to type this formula into a blank cell (at this example, we select Cell C1) and press Enter key, then copy this formula from cell C1 to range C2:C4.
Step2: Then you need to select range C1:C4, and right click on it, select Format Cells from the popup menu list. the Format Cells dialog will open.
Step3: Switch to Number tab in the Format Cells dialog box, and click Time from the Category list box, then select the standard time type in the Type list box. Click Ok button.
Step4: Let’s see the last result:
2. Convert Military Time to Standard Time using User Defined Function with VBA Code
If you want to convert Military Time to Standard Time using a User-Defined Function with VBA code, you can follow these steps:
Step1: Press ALT + F11 to open the Visual Basic Editor.
Step2: In the Visual Basic Editor, click on Insert -> Module to insert a new module.
Step3: n the new module, paste the following code. Save the module and return to the Excel workbook.
Function ConvertMilitaryToStandard_ExcelHow(MilitaryTime As String) As String
Dim Hour As Integer
Dim Minute As String
Dim Second As String
Dim StandardTime As String
Hour = Left(MilitaryTime, 2)
Minute = Mid(MilitaryTime, 3, 2)
Second = Right(MilitaryTime, 2)
If Hour = 0 Then
StandardTime = "12:" & Minute & ":" & Second & " AM"
ElseIf Hour < 12 Then
StandardTime = Hour & ":" & Minute & ":" & Second & " AM"
ElseIf Hour = 12 Then
StandardTime = "12:" & Minute & ":" & Second & " PM"
Else
StandardTime = Hour - 12 & ":" & Minute & ":" & Second & " PM"
End If
ConvertMilitaryToStandard_ExcelHow = StandardTime
End Function
Step4: In a blank cell, enter the following formula:
=ConvertMilitaryToStandard_ExcelHow(B1)Where B1 is the cell containing the Military Time.
Step5: Press Enter to see the converted time in Standard Time format.
The above VBA code converts the Military Time to Standard Time by extracting the hour and minute components of the Military Time and then using an If-Else statement to determine the corresponding Standard Time.
3. Convert Standard Time to Military Time Using Text Formula
If you want to convert Standard time to Military time, you can use another formula based on the TEXT function. Like this:
=TEXT(C1,"HHMMSS")Type this formula into cell D1, press Enter key on your keyboard. Drag the AutoFill Handle over to other cells to apply this formula.
4. Video: Convert Military Time to Standard Time in Excel
This video will show you how to convert military time to standard time in Excel using both a formula and VBA code.
5. Related Functions
- Excel TIMEVALUE Function
The Excel TIMEVALUE function returns the decimal number of the time represented by a text string. so it will convert a time represented by a text string into an Excel time value.The syntax of the TIMEVALUE function is as below:=TIMEVALUE (time_text)… - Excel MID function
The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)… - Excel LEFT function
The Excel LEFT function returns a substring (a specified number of the characters) from a text string, starting from the leftmost character.The LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])… - Excel RIGHT function
The Excel RIGHT function returns a substring (a specified number of the characters) from a text string, starting from the rightmost character.The syntax of the RIGHT function is as below:= RIGHT (text,[num_chars])… - Excel Text function
The Excel TEXT function converts a numeric value into text string with a specified format. The TEXT function is a build-in function in Microsoft Excel and it is categorized as a Text Function. The syntax of the TEXT function is as below: = TEXT (value, Format code)…
