Convert Number to Military Time in Excel (2 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

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

Simple Division and Custom Format to convert into military time

  • Then press the Enter button.

  • Finally, drag the Fill Handle icon downwards.

Simple Division and Custom Format to convert into military time

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.

Simple Division and Custom Format to convert into military time

  • Format Cells window appears.
  • Now, go to the Number tab and choose a Custom format.

Simple Division and Custom Format to convert into military time

  • 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.

Simple Division and Custom Format to convert into military time

  • 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)

Simple Division and Custom Format to convert into military time

  • 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)

Simple Division and Custom Format to convert into military time

  • 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")

TEXT Function to Convert Number to Military Time

  • 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"),".","")

Combine TEXT & SUBSTITUTE Functions to convert military time

  • 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.


Related Articles

Alok Paul
Alok Paul

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo