This article will illustrate how to convert number to military time in Excel. Have a look at the following image to get an overview.
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.
How to Convert Number to Military Time in Excel: 2 Methods
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 at 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.
Read More: Convert Number to Hours and Minutes in Excel
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
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 Decimal to Minutes and Seconds in Excel
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: How to Convert 4 Digit Number to Time in Excel
2.2 Combine TEXT & SUBSTITUTE Functions for Decimal Numbers
When we have fractional values in the number single TEXT function is not capable of converting 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
💬 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.
Download Practice Workbook
Download the following practice workbook to exercise while you are reading this article.
Conclusion
In this article, we described 2 methods to convert number to military time in Excel. I hope this will satisfy your needs. If you have any queries or suggestions, please let us know in the comment section below.
Related Articles
- Convert Number to Time hhmmss in Excel
- How to Convert 5 Digit Number to Time in Excel
- How to Convert Decimal to Time in Excel Using Formula
- How to Convert Decimal to Time Over 24 Hours in Excel
- How to Convert Decimal to Days Hours and Minutes in Excel
<< Go Back to Convert Number to Time | Time Conversion | Date-Time in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!