Convert Military Time to Standard Time in Excel (2 Suitable Ways)

Get FREE Advanced Excel Exercises with Solutions!

If you are searching for a solution or some special tricks to converting military time to standard time in Excel then you have landed in the right place. There are 2 ways to convert military time to standard time in Excel. This article will show you every step with proper illustrations so you can easily apply them for your purpose. Let’s get into the central part of the article.


What Is Military Time?

Military time is different from the standard format of time that we use. Generally, we use the 12 hours time format and the hour and minute values are separated by a semicolon between them.

For example, the standard format of the time is “HH:MM:SS AM/PM”.

But in the military, they use a different format. They usually use the 24 hours format and when the expressed time is in written form there isn’t any separation between the hour and the minute value. So, the format of the Military time is “HHMMSS”.

Why Would Someone Use the Military Time Format?

The main advantage of the military time format is that there will not create any confusion between the AM/PM. So, where the confusion between AM/PM can create a large effect like military, train, or air flight schedules, there the military system time format is used.


Military Time to Standard Time Chart

This chart is showing the relation between Military time and standard time. So, you can easily convert the time.

Chart of Converting Military Time to Standard Time in Excel


Convert Military Time to Standard Time in Excel: 2 Suitable Ways

In this section, I will show you 2 quick and easy ways to convert military time to standard time in Excel on Windows operating system. You will find detailed explanations with clear illustrations of each thing in this article. I have used Microsoft 365 version here. But you can use any other versions as of your availability. If anything of this article doesn’t work in your version then leave us a comment.

Convert Military Time to Standard Time in Excel - Dataset


1. Convert Military Time HHMMSS to Standard Time with Formula in Excel

Suppose, you have a dataset where the times are formatted in the 24 hours military system like HHMMSS. So, these values are unusable for any calculations related to time. And, you want to convert the time format into a standard 12 hours time format like HH:MM:SS AM/PM. For this, follow the steps below.

📌 Steps:

  • Here, in cells of range D6:D13, there are time values in Military format. And, I want to get the time values in Standard format in cell range E5:E13.
  • For this, insert the following formula into cell E6:
=TIMEVALUE(LEFT(D6,2)&":"&MID(D6,3,2)&":"&RIGHT(D6,2))

🔎 Formula Breakdown:

  • LEFT(D6,2) = 22 : The LEFT function extracts the hour value from the time in military format.
  • MID(D6,3,2) = 12 : The MID function extracts the minute value from the time value of cell D6. It extracts 2 characters after 1st 3 characters of the cell text.
  • RIGHT(D6,2) = 02 : The RIGHT function extracts the second value from the time.
  • LEFT(D6,2)&”:”&MID(D6,3,2)&”:”&RIGHT(D6,2) = 22:12:02 : Thus, the hour, minute and second values are separated by semicolon between them. Now, they are in Text format.
  • TIMEVALUE(22:12:02) = 0.925023148 : The TIMEVALUE function converts the time in text format to time But as the cells are in General format, there will show the numerical value of the time.

Applying Formula to Convert Military Time to Standard Time in Excel

  • Now, drag the Fill Handle icon to paste the used formula respectively to the other cells of the column or use Excel keyboard shortcuts Ctrl+C and Ctrl+V to copy and paste.

Dragging fill handle icon

  • As a result, I have got some numerical values in cell range E6:E13. So, now, I have to convert the numerical time values to Time format.
  • For this, select the cells of range E6:E13.
  • Then, go to the Home tab and click on the drop-down arrow beside the Cell Format box.
  • Then, choose the “More Number Format” option from the list.

Cinverting to Time Format

  • Now, a pop-up window named “Format Cells” will appear.
  • Click on the Time option under the Category
  • Then, select the 4th option from the Type box which is showing “1:30:55 PM”. It is the Standard time format that we use for regular purposes.
  • After that, press the OK

Custom Formatting Window

  • As a result, you will see that the military times are converted into the standard time format in cells E6:E13.

Convert Military Time to Standard Time in Excel

Read More: How to Convert Time to Number in Excel


2. Convert 24 Hours Military Time to 12 Hours Standard Time

Sometimes, people use the military format where the hour and minute values are separated by a semicolon between them. So, in this case, you just have to convert the 24 hours format time to 12 hours format to get the military time in the standard format. For this, follow the steps below.

📌 Steps:

  • Here, cells of range D6:D13 are containing the time value in 24 hours format.

Dataset of Converting 24 Hours Military time to 12 Hours Format Standard Time

  • Now, copy the cells of D6:D13 and paste them into the cells of E6:E13. So, you can easily compare them.

Copying cells to new column to Convert 24 Hours Military time to 12 Hours Format Standard Time

  • First, select the cells of range E6:E13. Then go to the Home tab in the top ribbon.
  • Then, click on the drop-down arrow beside the Cell Format box.
  • Here, select the “More Number Format” option from the list.

going to more number formats

  • Now, in the Format Cells window, click on the Time option in the category list.
  • Then, select the 4th option from the Type box again and press OK.

Custom formatting window

  • As a result, you will get the cell values in the standard time format which is the 12 hours time format with AM/PM

Convert 24 Hours Military time to 12 Hours Format Standard Time

Read More: How to Convert Time to Hours in Excel


Download Practice Workbook

You can download the practice workbook from here:


Conclusion

In this article, you have found how to convert military time to standard time in Excel. I hope you found this article helpful. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles


<< Go Back to Time Conversion | Date-Time in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Osman Goni Ridwan
Osman Goni Ridwan

OSMAN GONI RIDWAN is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, OSMAN has transitioned into the role of a content developer. Specializing in creating technical content exclusively centered around Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo