# How to convert military time to standard time in Excel

You can watch a video tutorial here.

Excel treats date and time values differently from numbers and text and provides several formats for displaying dates and times. Military time uses the 24-hour clock instead of the usual 12-hour clock with the ‘AM’ and ‘PM’ differentiator. Excel does not recognize military time as a time format, so the time has to be converted using text and time functions in Excel.

The approach is as follows:

1. Convert the military time string to a hh:mm:ss time format by introducing colons (:) between each set of 2 characters. The following functions are used to extract each set of characters and the ampersand (&) operator joins all pieces together:
a. LEFT() function: this returns a specified number of characters from the left of a string
i. Syntax: LEFT(text, number of characters)
1. text: the string from which the characters are to be extracted
2. number of characters: the number of characters to extract
b. MID() function: this returns a specified number of characters from a string
i. Syntax: MID(text, start number, number of characters)
1. text: the string from which the characters are to be extracted
2. start number: the number of the character from which the extraction is to start
3. number of characters: the number of characters to extract
c. RIGHT() function: this returns a specified number of characters from the right of a string
i. Syntax: RIGHT(text, number of characters)
1. text: the string from which the characters are to be extracted
2. number of characters: the number of characters to extract

2. Convert the resulting time format to a decimal value using the following function:
a. TIMEVALUE(): this returns the decimal number of a time represented as a string.
i. Syntax: TIMEVALUE(time string)
1. time string: a string representing time in any of the Excel time formats

3. Format the decimal value in a standard time format

Total Time:

### Step 1 – Convert the military time to a time format

– Select the cell where the result is to appear
– Type the formula using cell references:
=LEFT(Military time,2)&”:”&MID(Military time,3,2)&”:”&RIGHT(Military time,2)
– Press Enter
– Colons are introduced between each set of 2 characters to present an ‘hh:mm:ss’ time format

### Step 2 – Change the time into a decimal value

– Select the cell with the formula
– Press F2 to edit the cell or place the cursor in the formula bar
– Amend the formula by adding the TIMEVALUE() function:
=TIMEVALUE(LEFT(Military time,2)&”:”&MID(Military time,3,2)&”:”&RIGHT(Military time,2))
– Press Enter

### Step 3 – Copy the formula

– Using the fill handle from the first cell, drag the formula to the remaining cells
OR
a) Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
b) Select the rest of the cells in the column and press Ctrl+V or choose Paste from the context menu (right-click)

### Step 4 – Open the Format Cells window

– Right-click and select Format Cells  from the context menu
OR
Go to Home > Number  and click on the arrow to expand the menu
OR
Go to Home > Cells > Format > Format Cells
OR
Press Ctrl+1

### Step 5 – Select the format

– Go to the Number tab
– Select the Time category
– Choose a standard time format
– Click OK

### Step 6 – Check the result

– The military time is converted to standard time