# How to Convert Military Time to Standard Time in Excel

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.

## Convert Military Time to Standard Time

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))`

#1 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.

#2 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.

#3 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.

#4 Let’s see the last result:

## Convert Standard Time to Military Time

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.

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

How to Create Increment Number with Texts in Excel

Sometimes we want to fill 1,2,3,4,5… into each cell in a column, for implement this we can enter 1 in the first cell and then drag fill handle down to fill the following cells. But if there are some texts ...

How to Generate Random Time in Excel

In some special situations we may need to generate some random times in worksheet. We can enter time manually and one by one, but if we want to require a lot of random dates in excel worksheet, we will spend ...

How to Convert Text to Time in Excel

This post will guide you how to convert time string to time in Excel. Assuming that you have a list of text string in your worksheet, and you wish to convert these time string to standard time format, how to ...

How to Combine Text and Date into one Cell in Excel

This post will guide you how to combine text and dates in the same cell in Excel. How do I combine text and date values into one Cell in Excel 2013/2016. Combine Text and Date into One Cell Assuming that ...

How to Highlight Cell or Row If Date Is In Current Day/Week/Month in Excel

This post will guide you how to highlight cell if date is the current day or is in the current week or month in Excel. How do I highlight row if date is in current week or month with conditional ...