How to Convert a Number to Military Time in Excel (2 Methods)

What Is Military Time?

Military time employs a 24-hour format, consisting of four digits. The first two digits indicate the hour, while the last two represent the minutes. There is no distinction between AM and PM, and no colon is used. The time range spans from 0000 to 2359.

Dataset Overview

Let’s examine the dataset below, which contains various numbers. Our objective is to convert these numbers into military formatted time using Excel functions. Without delay, let’s explore the methods step by step.

Method 1 – Convert Number to Military Time by Applying Custom Format

1.1 If Numbers Mean Hours

If the numbers represent hours, follow these steps:

• Add a new column to the right of your dataset.

• In cell C5 (assuming your data starts in cell B5), enter the formula:

• Press Enter.

• Drag the Fill Handle icon downwards to apply the formula to other cells.

• The result will be in decimal format (e.g., 1.5 for 1 hour and 30 minutes).
• To display it in proper military time format, select all the cells, right-click and choose Format Cells.

• Go to the Number tab and select a Custom format.

• 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

If the numbers represent minutes, follow these steps:

Steps:

• Select all the cells containing the numbers.
• Press Ctrl+1 to open the Format Cells window.

• Choose the desired time format (e.g., hh:mm).
• Click OK.

• In cell C5, enter the formula:

This formula will convert minutes to decimal hours.

• Press Enter and drag the Fill Handle icon to apply the formula.

Read More: How to Convert Number to Minutes in Excel

1.3 If Numbers Mean Seconds

If the numbers represent seconds, follow these steps:

• Change the cell format as shown previously.
• In cell C5, enter the formula:

This formula will convert seconds to decimal hours.

• Press Enter and drag the Fill Handle icon.

Read More: How to Convert Decimal to Minutes and Seconds in Excel

Method 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. Here’s how to use it:

• Go to cell C5.
• Enter the formula based on the TEXT function with a format to present hour and minute in military time.

• Press Enter.
• Drag the Fill Handle icon to apply the formula to other cells.

Read More: How to Convert 4 Digit Number to Time in Excel

2.2 Combine TEXT & SUBSTITUTE Functions for Decimal Numbers

When dealing with fractional values, the single TEXT function may not convert them into military time. Combining TEXT and SUBSTITUTE can solve this problem:

• Go to cell C5 and enter the following formula:

• Press Enter.
• Drag the Fill Handle icon.

Things to Remember

When using the TEXT function, the first two digits must be below 24, and the second two digits must be below 60; otherwise, you’ll encounter an error in the result.

Download Practice Workbook

You can download the practice workbook from here:

Related Articles

• Convert Number to Time hhmmss 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