In Excel, convert a four-digit military-time entry stored as text in A2 into a real time with =TIME(VALUE(LEFT(A2,2)),VALUE(RIGHT(A2,2)),0). Format the result as hhmm to display, for example, 1730; use a colon format such as hh:mm if you want 17:30 instead.
What military time means in Excel
“Military time” usually means the 24-hour clock: hours run from 00 through 23 rather than using AM and PM. Excel does not have a special military-time data type. It stores a time as a fraction of a day and uses a number format to decide how that value looks in a cell. Midnight is 0, noon is one-half, and 18:00 is three-quarters of a day.
That distinction between a cell’s value and its display is important. A cell containing a real Excel time can appear as 6:00 PM, 18:00, or 1800 depending on its format, while remaining the same underlying time. Conversely, the text “1800” is just four characters. Excel cannot reliably use that text for time arithmetic until it is converted.
In everyday spreadsheets, military time is often written without a colon as four digits: 0730, 1200, or 2145. The leading zero is significant for consistent four-character entries, but Excel may remove it when it interprets an entry as a number. Decide whether the source data is text or real time before choosing a conversion method.
Convert four-digit text into a real Excel time
For a column of consistently formatted four-character text values, such as 0730 and 1730, use the conversion formula in a neighboring column. This version assumes A2 contains text with exactly two hour digits followed by two minute digits:
=TIME(VALUE(LEFT(A2,2)),VALUE(RIGHT(A2,2)),0)
For example, if A2 contains the text “1730,” the formula returns a real Excel time equivalent to 17:30. If A2 contains “0730,” it returns 07:30. The formula reads the first two characters as the hour, reads the last two as the minutes, and sets seconds to zero. Fill the formula down to convert other rows.
Format the results as four digits to remove the colon: select the result cells, press Ctrl+1 on Windows, choose Number > Custom, and enter hhmm in the Type box. Select OK. On Mac, use Command+1 to open Format Cells; the custom number format works the same way. To display a colon, enter hh:mm instead. The format changes only the appearance, not the stored time.
Excel formulas use the same syntax in Microsoft 365 for Windows and Mac. In Excel for the web, select the cells and use the number-format controls on the Home tab; if the custom format option is unavailable in your interface, open the workbook in the desktop app to set the custom format.
Enter military time without losing leading zeros
When users type 0730 into a General-formatted cell, Excel may treat it as the number 730 and discard the leading zero. That creates a problem for a formula expecting exactly four characters. Preserve the source as text if the entries are intended to be four-digit codes rather than already-recognized Excel times.
- Before entering or pasting the values, select the input cells and set their format to Text using Home > Number Format > Text.
- Alternatively, type an apostrophe before an individual entry, as in
'0730. Excel displays 0730, while the apostrophe marks the entry as text and is not displayed in the cell. - When importing a file, check the preview and import settings so the time-code column is treated as text. Do not assume a displayed leading zero means the underlying value is text; a custom number format can also show zeros that are not stored in the number.
If the values have already been converted to numbers, inspect the column before applying a formula. A numeric 1730 can still be read as four digits by a text-extraction formula, but numeric 730 has lost the information that distinguishes 0730 from a three-digit entry. You may be able to restore the intended four-character form with a format or a formula, but first confirm that every short value truly means an hour with a leading zero and not a different entry.
Display a real time as military time text
Sometimes the cell already contains a valid Excel time and the goal is to create a text label for a report, export, or message. If B2 contains a real Excel time, this formula returns a four-character text string:
=TEXT(B2,"hhmm")
For example, if B2 is 17:30, the result is the text “1730”; if it is 07:30, the result is “0730.” To include a colon, use =TEXT(B2,"hh:mm"), which returns text such as “17:30.” This is different from applying a custom number format: a custom format changes how the original numeric time is displayed, while TEXT creates a separate text result.
Use TEXT when the output must be text, such as when combining a time with a label or producing a fixed-format export. If the result will be used for calculations, keep the real time value and format that cell instead. Text results do not behave like numeric times in arithmetic and may need conversion again when imported into another system.
Check the input and handle invalid times
The basic TIME formula is convenient, but it does not validate that the source represents a legal time. Excel’s TIME function can normalize out-of-range inputs: an hour value of 24 or a minute value of 60 can roll into another hour or day rather than producing a clear input warning. That may hide a typo. Check that entries have four digits and that the hour is 00–23 and the minute is 00–59 before relying on the converted values.
For a quick manual check, filter the source column and look for entries with the wrong length, letters, spaces, or values such as 2460. A formula that returns an error for malformed source text can also help, but a separate validation check is clearer for a shared data-entry sheet. For example, Excel’s Data Validation feature can restrict entries, though the appropriate rule depends on whether users enter text codes or actual time values. Explain the expected format beside the input column so people do not mix 730, 0730, and 7:30.
Also distinguish a valid time from a duration. 24:30 is not a time of day on a 24-hour clock, but it can be a valid elapsed duration of 24 hours and 30 minutes. For durations, use an elapsed-hours number format such as [h]:mm; ordinary hh:mm displays the hour portion on a clock and can wrap after 24 hours.
Calculate with military times and overnight shifts
Once start and end entries have been converted to real Excel times, Excel can calculate differences. Suppose B2 contains a real start time and C2 contains a real end time. This formula calculates elapsed time, including a shift that crosses midnight:
=MOD(C2-B2,1)
If B2 is 23:00 and C2 is 01:30, the result is 2 hours and 30 minutes. Format the result as [h]:mm to show the duration clearly. MOD wraps a negative time difference into the next day, so the formula is useful for a single overnight interval. It assumes the end time is on the same day or the following day; it cannot tell whether an interval spans multiple days without date information.
For a normal daytime shift, subtracting the start from the end also works. For work records covering multiple dates, store the date as well as the time and calculate using full date-and-time values. Otherwise, an end time earlier than a start time could mean either “the next day” or a data-entry mistake. If you need paid hours, remember that breaks, rounding rules, and workplace policies are separate from the time conversion itself.
Choose the right method for your data
The best method depends on whether Excel already recognizes the value as a time and whether the final result must remain numeric. Use these distinctions to avoid converting a cell unnecessarily:
- Four-digit text such as “1730”: Convert it with TIME and then apply a time format. This produces a value suitable for calculations.
- A real time displayed as 5:30 PM: Do not parse it as four characters. Apply the custom
hhmmformat to display 1730 while keeping it numeric. - A real time that must become text: Use TEXT with a format such as
hhmmorhh:mm. - An elapsed duration over 24 hours: Keep it numeric and use
[h]:mm, rather than treating it as a time of day.
A useful test is to change the cell’s number format temporarily to General. A real time usually appears as a decimal fraction, while text remains a text string and a plain number such as 1730 remains 1730. This check helps identify why a formula or subtraction is not behaving as expected.
Common Excel military-time problems
The result shows a decimal such as 0.7291667. The conversion may be correct; the cell is simply using General or Number format. Apply hhmm or hh:mm.
The formula gives the wrong time for 0730. Check whether Excel stored the input as 730 or whether the input has spaces. Keep four-digit source values as text, then confirm the formula references the correct cells.
A result appears as 5:30 AM instead of 17:30. The cell is using a 12-hour format. Set the custom format to hh:mm; the lowercase 24-hour hour code displays 00 through 23.
A subtraction returns a row of hash marks or an unexpected negative result. Check whether the end time is earlier than the start time, whether the cells are real times, and whether the result is formatted as a time or duration. For an overnight interval, use the MOD method and a duration format.
Excel changes a value when it is typed. Excel may automatically recognize or reformat entries. Set the input column to Text before entering text codes, or enter genuine Excel times using a colon, such as 17:30, and then apply a display format.
Frequently Asked Questions
How do I change 1730 to 17:30 in Excel?
If 1730 is text in A2, use =TIME(VALUE(LEFT(A2,2)),VALUE(RIGHT(A2,2)),0), then format the result as hh:mm. If it is already a real time, just apply that number format.
How do I show 7:30 AM as 0730?
Select the real-time cell, open Format Cells with Ctrl+1 on Windows or Command+1 on Mac, and set the custom format to hhmm. This displays a leading zero without changing the time value.
Is 12:00 military time noon or midnight?
12:00 is noon in the 24-hour clock. Midnight is 00:00 at the start of a day. In four-digit form, those are 1200 and 0000.
Why does Excel show 0.5 for 1200?
Excel stores time as a fraction of a 24-hour day, and noon is half a day, or 0.5. Apply a time format such as hh:mm to display it as 12:00.
Can I type 2400 for midnight?
In ordinary 24-hour time notation, the next midnight is written 0000, not 2400. Excel may interpret hour 24 as the start of the next day, so use a date as well when the day boundary matters.
Related
- Excel Middle Years Academy: What Families Should Know
- Excel Middle Row Selection: Find and Select the Center Row
- Excel Power Query: Combine Columns Without Losing Data
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Remove Formatting Marks in Word: Show or Hide ThemTo remove formatting marks from view in Word, press Ctrl+Shift+8 on Windows or Command+8 on Mac, or select…
- Excel Data Validation List from a Table: How to Set It UpYes. In Excel for Microsoft 365 on Windows, turn a table column into a drop-down list by creating…
- Can You Automate Goal Seek in Excel? Methods That WorkYes. In Excel for Microsoft 365 on Windows, you can automate Goal Seek with a VBA macro; in…
- How to Delete a Page in Word Using a Keyboard ShortcutTo delete a page in Word on Windows, press Ctrl+G, type \page in the page-number box, press Enter,…
- Google Sheets QUERY Formula: Syntax, Examples, and TipsIn Google Sheets, the QUERY formula filters, sorts, selects, and summarizes a table using a query written in…
- How to Create a Badge Template in Word for PrintingTo create a badge template in Word, set up a page for your badge inserts, place a precisely…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.