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