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.
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.
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.
- 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:
🔎 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.
- 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.
- 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.
- 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
- As a result, you will see that the military times are converted into the standard time format in cells E6:E13.
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.
- Here, cells of range D6:D13 are containing the time value in 24 hours format.
- Now, copy the cells of D6:D13 and paste them into the cells of E6:E13. So, you can easily compare them.
- 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.
- 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.
- As a result, you will get the cell values in the standard time format which is the 12 hours time format with AM/PM
Read More: How to Convert Time to Hours in Excel
Download Practice Workbook
You can download the practice workbook from here:
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.