How to Convert Military Time in Excel (24-Hour Clock)

To convert military time in Excel, you turn a number like 1430 into a real time (2:30 PM) that Excel can format and calculate with. Military time is the 24-hour clock written as four digits with no colon. The core formula uses TEXT to add a leading zero, LEFT for the hours and RIGHT for the minutes. This guide shows that formula, a shorter version, how to go back the other way, and how to get the hours between two military times.

How Excel stores time#

Excel stores a time as a fraction of a day. 12:00 PM is 0.5, 6:00 AM is 0.25 and 6:00 PM is 0.75. The format you apply only changes how that fraction looks.

When you type 1430 into a cell, Excel doesn't see a time. It sees the number one thousand four hundred and thirty. If you format it as a time, the cell shows 0:00, because 1430 means 1,430 whole days with no time part. That's why military time needs a formula first.

Military times also lose their leading zero when typed as numbers. You type 0930, and Excel stores 930. The formulas below handle that.

Convert military time to a real Excel time#

Use TIME, which builds a time from hours, minutes and seconds. With the military time in A2:

=TIME(LEFT(TEXT(A2,"0000"),2),RIGHT(A2,2),0)

Here is what each part does:

Then format the result: press Ctrl+1 (Mac: ⌘1), choose Time, and pick a format such as 1:30 PM. These are the results for common values:

The TEXT function guide, LEFT function guide and RIGHT function guide cover each function's arguments.

A shorter formula#

TEXT can add the colon for you, and Excel can read the result as a time:

=--TEXT(A2,"00\:00")

TEXT(A2,"00\:00") turns 930 into the text "09:30". The backslash tells Excel to show the colon as a plain character. The double minus (--) turns that text into a number, the time 9:30 AM. Format the cell as a time, as before.

When the military time is stored as text#

If your data came from another system, the times may already be text with the leading zero, such as "0930". Text sits on the left of the cell by default. In that case, skip TEXT and split the text directly:

=TIME(LEFT(A2,2),RIGHT(A2,2),0)

To check what you have, use =ISTEXT(A2): TRUE means text. The guide to converting text to dates covers the same problem for dates.

If your times have seconds too, like 143015, make them six digits and take the middle with MID:

=TIME(LEFT(TEXT(A2,"000000"),2),MID(TEXT(A2,"000000"),3,2),RIGHT(A2,2))

That returns 2:30:15 PM.

Show times in 24-hour format#

You don't need a formula to display a real time on the 24-hour clock. Change the number format:

1. Select the cells with real times.
2. Press Ctrl+1 (Mac: ⌘1) and choose Custom.
3. Type hh:mm for 14:30, or hhmm for 1430 with no colon.

The value stays the same; only the display changes. The guide to custom number formats explains every code, and leaving out AM/PM is what makes Excel use the 24-hour clock.

Convert a normal time back to military time#

To turn a real time into military time as text, use TEXT with the hhmm format:

=TEXT(B2,"hhmm")

9:30 AM becomes "0930" and 2:30 PM becomes "1430". The result is text, so it keeps the leading zero, which is what you want for reports and system uploads. If you need a number, wrap it in VALUE, but 0930 then becomes 930. The guide to TEXT function formats lists more format codes.

Hours between two military times#

Convert both times first, then subtract. For a shift that might cross midnight, wrap the subtraction in MOD:

=MOD(C2-B2,1)*24

If B2 is 10:00 PM (from 2200) and C2 is 6:00 AM (from 0600), plain subtraction gives a negative number. MOD with 1 wraps it into the next day, giving 0.3333 of a day. Multiply by 24 to get 8 hours. The guides to time differences and adding hours to a time cover totals over 24 hours and other edge cases.

Common military time mistakes#

These cause most wrong results:

1. Formatting the number as a time. 1430 shows as 0:00. Convert it with a formula first.
2. Using LEFT without TEXT. =LEFT(930,2) returns "93", not "09". Always pad the number to four digits.
3. Midnight written as 2400. The TIME formula turns 2400 into 0, which is 12:00 AM; the shorter --TEXT formula returns 1, which is a full day. Change 2400 to 0000 in the data, or test for it with IF.
4. Mixing text and numbers. A column that mixes "0930" text with 1430 numbers needs the TEXT version, which works on both.
5. Expecting AM/PM in the result of TEXT. TEXT(B2,"hhmm") returns text. You can't do time maths on it until you convert it back.

Practise it#

Converting military time is really text work: padding with TEXT and splitting with LEFT and RIGHT. Practise TEXT and VALUE in a live spreadsheet, then practise LEFT, RIGHT, MID and LEN to split any code or time. Each lesson checks your formula as you type, and the exercise on this page converts a real military time.