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.

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:

=B5/24

Simple Division and Custom Format to convert into military time

Press Enter.

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

Simple Division and Custom Format to convert into military time

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.

Simple Division and Custom Format to convert into military time

Go to the Number tab and select a Custom format – hhmm.

Simple Division and Custom Format to convert into military time

Press OK.

We get the result in the proper time format.

1.2 If Numbers Mean Minutes

If the numbers represent minutes, follow these steps:

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

Simple Division and Custom Format to convert into military time

Choose the desired time format (e.g., hhmm).
Click OK.

In cell C5, enter the formula:

=B5/(24*60)

This formula will convert minutes to decimal hours.

Simple Division and Custom Format to convert into military time

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

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:

=B5/(24*60*60)

This formula will convert seconds to decimal hours.

Simple Division and Custom Format to convert into military time

Press Enter and drag the Fill Handle icon.

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.

=TEXT(B5,"00\00")

TEXT Function to Convert Number to Military Time

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

2.2 Combine the 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:

=SUBSTITUTE(TEXT(B5,"00.00"),".","")

Combine TEXT & SUBSTITUTE Functions to convert military time

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.