Convert Military Time to Standard Time in Excel (Formulas)

A lot of organizations prefer to use the 24-hour ‘military time’. These include the airline industry, public transport, and of course the armed forces.

Besides organizations, there are a lot of people who prefer this time format too, for the simple reason that it represents time in an absolute format, avoiding any confusion between AM and PM.

Like most people, you might prefer the simpler ‘Standard time’ format that divides the day into two parts, distinguishing each part with an AM or PM. 

If you have a worksheet where time is shown in military time format, you might not be able to find a direct function in Excel that can convert it to standard time.

But there is a way around this, albeit involving a few more steps.

In this tutorial, we will show you how to convert Military time to Standard time in Excel.

The military time could be represented in a shorter form, with just hours and minutes displayed(eg: 2230), or a longer form, with hours, minutes, and seconds displayed (eg: 223010).

We will show you how to tackle both forms. We will also show you how to convert time back to military format, in case you need to.

Converting Short Form of Military Time to Standard Time in Excel

The short form of military time displays the time with just 4 digits – the hours and the minutes.

For example, consider the following list of military times represented in short form:

Military time in 4 digit format

To convert these to standard time, follow the steps below:

  1. In a blank cell (where you want the standard time displayed), type the formula: =TIMEVALUE(LEFT(A2,2)&”:”&RIGHT(A2,2)).
  2. Press the Return key. You should now see the time displayed in the form of a decimal number.
  3. Drag down the fill handle to copy the formula to the rest of the cells in the column.
Formula to turn military time to decimal
  1. Make sure all the cells containing the time in decimal form are selected.
  2. Right-click on your selection.
  3. From the context menu that appears, select ‘Format Cells’. 
Right-click and then click on Format cells
  1. This will open the Format Cells dialog box.
  2. Make sure the Number tab is selected.
  3. From the Category list on the left, select the ‘Time’ option.
Select the time format in the Format Cells dilaog box
  1. On the right side of the dialog box, under ‘Type’, you should see a number of options for different time formats.
  2. Select the time format that you prefer. We selected the format “1:30 PM”.
Select the time format
  1. Click OK to close the dialog box.

You should now see the time displayed in Standard Time format.

Military time converted to standard time

Explanation of the Formula and Steps

Let us try and understand the formula that we used. The formula consists of 3 parts or 3 functions:

  • The LEFT function returns a given number of characters in a string, starting from the left. So LEFT(A2,2) extracts the first 2 digits from the time in cell A2 (which is 22 in our example).
  • Similarly, the RIGHT function returns a given number of characters in a string, starting from the right. So RIGHT(A2,2) extracts the last 2 digits from the time in cell A2 (which is 30 in our example).
  • The ampersand operator (&) concatenates two strings, so LEFT(A2,2)&”:”&RIGHT(A2,2) simply combines the result of the two functions, giving the text 22:30.

Now since this is a string, we cannot use it to perform computations related to time. So we need to convert it to a TIME value.

For this, we use the TIMEVALUE function.

So, TIMEVALUE(LEFT(A2,2)&”:”&RIGHT(A2,2)) returns the string in the form of a decimal value that can be understood as Time in Excel.

The problem is that the function returns the time as a fraction of a 24-hour day.

For example, if you pass the value “12:00:00” to the function, it returns 0.5 because 12 hours is half a day. Similarly, in our example, we get the time value 0.9375.

But we don’t want to see the time like this. We want to see it in standard time format. So for this, we change the format of the time using Format Cells (in steps 4 to 11).

Converting from Long Form of Military Time to Standard Time in Excel

The long-form of Military time displays the time with 6 digits – the hours, minutes, and seconds. For example, consider the following list of military times represented in long-form:

Military time in 6 digit format

To convert these to standard time, follow the steps below:

  1. In a blank cell (where you want the standard time displayed), type the formula: =TIMEVALUE(LEFT(A2,2)&”:”&MID(A2,3,2)&”:”&RIGHT(A2,2)).
  2. Press the Return key. You should now see the time displayed in the form of a decimal number.
  3. Drag down the fill handle to copy the formula to the rest of the cells in the column.
Formula to convert 6 digit military time to decimals
  1. Make sure all the cells containing the time in decimal form are selected.
  2. Right-click on your selection.
  3. From the context menu that appears, select ‘Format Cells’. 
  4. This will open the Format Cells dialog box.
  5. Make sure the Number tab is selected.
  6. From the Category list on the left, select the ‘Time’ option.
  7. On the right side of the dialog box, under ‘Type’, you should see a number of options for different time formats.
  8. Select the time format that you prefer. We selected the format “1:30:55 PM”.
Select the time format
  1. Click OK to close the dialog box.

You should now see the time displayed in Standard Time format.

Military 6 digit time converted to standard time

Explanation of the Formula

The formula used here consists of 4 functions:

The LEFT function returns a given number of characters in a string, starting from the left. So LEFT(A2,2) extracts the first 2 digits from the time in cell A2 (which is 22 in our example).

The RIGHT function returns a given number of characters in a string, starting from the right. So RIGHT(A2,2) extracts the last 2 digits from the time in cell A2 (which is 10 in our example).

The MID function returns a given number of characters in a string starting from a given character position. So MID(A2,3,2) extracts 2 digits from the time in cell A2, starting from the third character onwards. So in our example, the function returns 30.

The ampersand operator (&) concatenates three strings together, so LEFT(A7,2)&”:”&MID(A7,3,2)&”:”&RIGHT(A7,2) returns the text 22:30:10.

Since this is a string, we cannot use it to perform computations related to time. So we need to convert it to a TIME value by using the TIMEVALUE function.

Converting Standard Time to Military Time in Excel

Now, what if you want to reverse the process?

You might have converted the time to the standard format, have done some processing and computations with it and now want to convert the resultant time back to the military time format.

There are two ways to do this, and both methods involve simply changing the format of the cell.

Method 1: Using the TEXT Function to Convert Standard Time to Military Time

If you want to display the results in a separate column, then you can use the following formula (for cell A2):

=TEXT(A2,”HHMM”)

Here’s a screenshot of what happens when you apply this formula to a given list of military times:

TEXT formula to get military time format

The above formula converts the standard time into the short form of military time. If you want to convert it to the long-form military time instead then you can use the following formula:

=TEXT(A2,”HHMMSS”)

Here’s a screenshot of what happens when you apply this formula to a given list of military times:

TEXT formula to convert to 6 digit military time

The above two formulas take the time which is in cell A2 and break it down into its constituent parts –hours, minutes, and seconds. They then use the text string in the second parameter (“HHMM” or”HHMMSS”) to format the parts of the time.

So the formula replaces the “HH” part with the hours, “MM” part with the minutes, and “SS” with the seconds.

Note: This method will convert the time value into TEXT format, so your time will be displayed aligned to the left side, unless explicitly formatted in some other alignment.

Method 2: Using the Format Cells Dialog Box to Convert Standard Time to Military Time

If you want to make changes to the same cells, instead of creating a new column, then you can follow the steps below:

  1. Select the cells containing the standard times.
  2. Right-click on your selection and select ‘Format Cells’ from the context menu that appears.
  3. This opens the Format Cells dialog box.
  4. Make sure the Number tab is selected.
  5. From the Category list on the left, select the ‘Custom’ option.
  6. In the input box, under ‘Type’, you can type in the format you want to display the time in. So if you want to display in short form military time, type “HHMM” and if you want to display in long form, type “HHMMSS”
Custom format to get military format
  1. Click OK to close the dialog box.

You should now see the time displayed in Military Time format.

Standard Time in military format

Note: This method only changes the format in which the time is displayed. It does not change the underlying time. So your time will still be aligned to the right side, unless explicitly formatted in some other alignment. Moreover, if you click on the time, you can see the original time value in the formula bar.

Format to get the military format

In this tutorial, we showed you how to convert time from Military format to the Standard format and vice-versa.

We hope it was helpful for you.

Other Excel tutorials you may also like: