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.

Table of Contents

## 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)…

## Leave a Reply

You must be logged in to post a comment.