ExcelInsider Team
While most people use the 12-hour clock for their activities, there are scenarios where using military time is more relevant. However, not everyone is able to read military time. In a project, it might be required to convert military time to standard time in excel. In this article, we will learn three ways to do so.
➤ Select a helper column with the same rows
➤ Enter this formula into the first cell (without heading) of the helper column.
=TIME(LEFT(F2,2), RIGHT(F2,2),0)
➤ Change F2 to your input cell where the military time is written
➤ Autofill the other cells by dragging your mouse
However, this is not the only way military time can be converted. There is more to understand about the formula and the way you can change it to fit your dataset. Keep reading to learn about more ways to convert military time to standard time in excel.
Using the TIME function
In the dataset we are working with here, there are some logs of inventory management data. During the processing, the time was recorded as military time. We need to convert it to standard time to understand it better. Follow the methods below to do that.
The TIME function is able to convert military time to standard time, and you don’t even need to change the cell type to do so. This works with military time, that only mentions hours and minutes.
➤ In your excel sheet, choose a column that can work as your helper column to extract the standard time from military time.
➤ In the first cell after the heading, enter this formula:
=TIME(LEFT(F2,2), RIGHT(F2,2),0)
=TIME(LEFT(F2,1), RIGHT(F2,2),0)
Advanced Usage of the TIME function
In the previous example, there are some limitations if the character count differs in different cells. But in the current method, we won’t have any issues like that. This time, we will be modifying the TIME function to convert the time.
➤ Select a helper column just as before
➤ Write this formula to convert military time to standard time in excel
=TIME(ROUNDDOWN(F2/100,0),F2-(ROUNDDOWN(F2/100,0)*100),0)
➤ You know the drill, autofill the formula to other cells as well.
Formatting Full Military Time Including Seconds
The previous two examples dealt with hours and minutes only. But in some cases, there might be seconds to deal with as well. In that case, we will have to use the TIMEVALUE function.
➤ Take a helper column. This time, make sure that the number format is set to Time.
➤ Enter the following formula into the cell:
=TIMEVALUE(LEFT(F2,2)&”:”&MID(F2,3,2)&”:”&RIGHT(F2,2))
➤ Autofill the rest of the cells.
Frequently Asked Questions
How to do military time?
Usually, you need to add 12 hours to the PM (No addition needed for AM, remove the AM/PM sign) to get 24-hour time, and then remove the colon ( : ) to get the military time.
How to tell the military time in 24 hour format?
There are two differences between the 24-hour format and military time. Firstly, for a singular digit hour, military time will add a leading zero. Then it will remove the colon ( : ) sign.
How to convert standard time to military time in excel?
Use this formula to do so:
=TEXT(A1,”HHMM”)
Replace A1 with your input cell and press Enter.
How to convert military time to standard time in Google Sheets?
Use this formula:
=TEXT(VALUE(LEFT(F2,2)&”:”&RIGHT(F2,2)), “hh:mm AM/PM”)
You must replace F2 with your input cell.
How do I automatically insert current time in Excel?
Go to your desired cell, and press Ctrl+Shift+; .
Wrapping Up
Decoding military time to standard time can be a tough job. In this article, we have shown you three ways to convert military time to standard time in excel. To practice doing this, download the practice workbook, and don’t forget to comment down your suggestions.
