Alok Paul
This article will illustrate how to convert number to military time in Excel. Have a look at the following image to get an overview.
Download Practice Workbook
Download the following practice workbook to exercise while you are reading this article.
What Is Military Time?
The military number is 24-hour time format and is represented with 4 digits. First, 2 digits for the hour and the last 2 digits for minutes. No AM and PM division appears. There is no colon too. It starts at 0000 to ends at 2359.
2 Methods to Convert Number to Military Time in Excel
Have a look at the following dataset. We have some numbers here. Our task is to convert these numbers in the military formatted time by applying Excel features. Without any further discussion, let’s dive into the methods one by one.
1. Convert Number to Military Time by Applying Custom Format
Numbers in the dataset may be present in 3 formats of the time. Like hours, minutes, and seconds. Here, we will first divide the number and then change the format.
1.1 If Numbers Mean Hours
Here, numbers indicate the values of hours.
📌 Steps:
- First, add a column on the right side.
- After that, put the following formula in Cell C5.
=B5/24
- Then press the Enter button.
- Finally, drag the Fill Handle icon downwards.
We get the result. But not in proper military time format. To do that-
- Select all the cells.
- Then press the right button of the mouse.
- Choose the Format Cells option from the Context Menu.
- Format Cells window appears.
- Now, go to the Number tab and choose a Custom format.
- Finally, press OK.
We get the result in the proper time format.
1.2 If Numbers Mean Minutes
In the second case, the numbers are in minute format. Execute the following steps.
📌 Steps:
- First, we will change the cell format.
- First, select all the cells.
- Now, press Ctrl+1.
- Format Cells window appears.
- Choose the desired time format.
- After that, press the OK button.
- Now, go to Cell C5 and put the formula below.
=B5/(24*60)
- Press the Enter button and drag the Fill Handle icon.
Read More: How to Convert Number to Minutes in Excel (2 Easy Methods)
1.3 If Numbers Mean Seconds
In the 3rd case, the numbers here mean seconds. To convert them to military time, we will change the format first, and then divide the number by 24*60*60.
📌 Steps:
- First off, we change the Cell Format shown previously.
- Then put the following formula on Cell C5.
=B5/(24*60*60)
- Lastly, press Enter and drag the Fill Handle icon.
Read More: How to Convert 4 Digit Number to Time in Excel (3 Methods)
2. Use Excel Functions to Convert Number to Military Time
2.1 Use TEXT Function for Integer Numbers
The TEXT function converts a value to text in a specific number format. In this method, we will show the use of the TEXT function to do our task.
📌 Steps:
- Go to Cell C5.
- Put the formula based on the TEXT function with a format to present hour and minute in military time.
=TEXT(B5,"00\00")
- Now, press the Enter button and drag the Fill Handle icon.
Read More: Convert Number to Hours and Minutes in Excel (2 Easy Methods)
2.2 Combine TEXT & SUBSTITUTE Functions for Decimal Numbers
When we have fractional values in the number single TEXT function is not capable to convert that into military time. TEXT and SUBSTITUTE combination is capable of solving the problem.
📌 Steps:
- Go to Cell C5 and put the following formula.
=SUBSTITUTE(TEXT(B5,"00.00"),".","")
- Hit the Enter button and drag the Fill Handle icon.
Read More: How to Convert Fraction into Hours and Minutes in Excel (3 Ways)
💬 Things to Remember
When using the TEXT function, one thing that needs to remember is that the first 2 digits must be below 24 and the second 2 digits below 60. Otherwise, we will get an error in the result.
Conclusion
In this article, we described 2 methods to convert number to military time in Excel. I hope this will satisfy your needs. Please have a look at our website ExcelDemy.com and give your suggestions in the comment box.
