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:

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.

4. Make sure all the cells containing the time in decimal form are selected.
5. Right-click on your selection.
6. From the context menu that appears, select ‘Format Cells’. 

7. This will open the Format Cells dialog box.
8. Make sure the Number tab is selected.
9. From the Category list on the left, select the ‘Time’ option.

10. On the right side of the dialog box, under ‘Type’, you should see a number of options for different time formats.
11. Select the time format that you prefer. We selected the format “1:30 PM”.

12. Click OK to close the dialog box.

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

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

Also read: How To Combine Date and Time in Excel (Formulas) 

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:

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.

4. Make sure all the cells containing the time in decimal form are selected.
5. Right-click on your selection.
6. From the context menu that appears, select ‘Format Cells’. 
7. This will open the Format Cells dialog box.
8. Make sure the Number tab is selected.
9. From the Category list on the left, select the ‘Time’ option.
10. On the right side of the dialog box, under ‘Type’, you should see a number of options for different time formats.
11. Select the time format that you prefer. We selected the format “1:30:55 PM”.

12. Click OK to close the dialog box.

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

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.

Also read: How to Separate Date and Time in Excel (7 Easy Ways)

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:

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:

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.

Also read: How to Convert GMT to EST in Excel

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”

7. Click OK to close the dialog box.

You should now see the time displayed in Military Time 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.

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:

• How to Convert Days to Years in Excel (Formulas)
• How to Convert Date to Day of Week in Excel
• Convert Time to Decimal in Excel
• How to Add Days to a Date in Excel
• How to Convert Month Number to Month Name in Excel
• How to Convert Date to Month and Year in Excel
• How to Calculate Hours between two Times in Excel?
• How to Add Minutes to Time in Excel?