Military time, the four-digit 24-hour format that runs from 0000 at midnight to 2359 at 11:59 PM, becomes standard 12-hour time in Excel the moment you wrap a TIME() or TEXT() formula around the hour and minute and apply the h:mm AM/PM format mask. The conversion itself is just arithmetic: every hour from 13:00 to 23:00 equals the PM hour plus 12, midnight is 00:00 (which Excel must display as 12:00 AM, never 00:00 AM), and noon stays 12:00 (which must display as 12:00 PM, never 12:00 AM). Once that rule is clear, Excel only needs three pieces — the hour, the minute, and a TIME() call that Excel recognizes as a real serial time value — and the format mask does the visual work. This guide walks through the formulas that handle the most common ways military time shows up in a worksheet, calls out the exact moment the midnight edge case trips people up, and finishes with a faster browser-based option for anyone who doesn't want to type a formula at all.

how to convert military time to standard in excel
How to Convert Military Time to Standard Time in Excel

What Military Time Looks Like in an Excel Worksheet

Before a formula will help, you have to know which of three flavors of military time is sitting in your column. A cell can hold the digits as a plain number (1800, 0630, 0000), as a text string ("1800", "0630") that Excel imported but refuses to add to another number, or as a real 24-hour time value (18:00:00) that Excel already understands as a fraction of a day. The way you convert each one matters, because each storage type needs a slightly different formula — and an export from payroll, a CSV from an airline booking, and a manually typed shift roster will each land in a different shape.

Many real spreadsheets arrive with military time baked in: medication charts in hospitals, flight itineraries from booking sites, shift rosters in operations teams, and machine logs from industrial equipment. Excel reads the digits either way, but it cannot tell you on its own that 18:00 is the same moment as 6:00 PM. The conversion is up to you, and the cheapest version of that conversion is one formula copied down a column.

How to Convert Military Time to Standard Time in Excel

This is the dedicated Excel section. Pick the one that matches how your data is actually stored in the cell and follow the numbered steps exactly.

For military time stored as a number (1800, 0630, 0000)

  1. Click a blank cell in the row of your first military time, say B2 next to A2 = 1800.
  2. Type the formula =TEXT(TIME(INT(A2/100), MOD(A2,100), 0), "h:mm AM/PM"). INT(A2/100) pulls out the hour, MOD(A2,100) pulls out the minute, TIME() builds a real Excel time serial, and TEXT() with the "h:mm AM/PM" mask renders it as 12-hour text.
  3. Press Enter. B2 should display 6:00 PM. If it instead shows 0.75, you have a format problem rather than a formula problem — see step 4.
  4. Right-click B2, choose Format Cells, pick the Time category, and select a 12-hour format like h:mm AM/PM. Press OK.
  5. Copy B2 down the column by selecting it and double-clicking the fill handle (the small square at the bottom-right of the cell), or by pressing Ctrl+C, selecting the rest of the column with Ctrl+Shift+Down, and pressing Ctrl+V.

Walked through with 1800 as the example: INT(1800/100) returns 18, MOD(1800, 100) returns 0, TIME(18, 0, 0) returns the Excel serial 0.75 (which is 18 hours out of 24), and TEXT(0.75, "h:mm AM/PM") formats it as "6:00 PM". That single substitution is the entire conversion.

For military time stored as text ("1800", "0630")

If your military time came in as a quoted string — common with CSV imports — the same idea works with one extra step that turns the text into a real time:

  1. In B2 next to A2 = "1800", type =TEXT(TIMEVALUE(LEFT(A2,2)&":"&RIGHT;(A2,2)), "h:mm AM/PM"). LEFT grabs the first two digits as the hour, RIGHT grabs the last two as the minute, the ampersand stitches them together with a colon, TIMEVALUE reads that as a time, and TEXT formats it as 12-hour text.
  2. Press Enter and confirm B2 reads 6:00 PM.
  3. Format B2 as h:mm AM/PM the same way as in step 4 above (in most cases TEXT() already returns text, so this step is a safety net).
  4. Fill the formula down the column with the fill handle.

For military time already stored as a 24-hour time value (18:00:00)

If clicking the cell shows a real Excel time like 18:00:00 in the formula bar, you don't need a formula at all. Select the cells, press Ctrl+1 to open Format Cells, pick the Time category, and choose any format that includes AM/PM. Excel does the rest.

Convert Standard Time Back to Military Time in Excel

The reverse direction is just as common — you have a column of 12-hour times and you need 24-hour output for a system export. The TEXT function handles it in a single call, and the choice of formula again depends on how the input is stored.

  • If A2 holds a real Excel time (you see 0.25 in the formula bar for 6:00 AM), use =TEXT(A2, "HHMM") to get "0600".
  • If A2 holds text like "6:00 AM", use =TEXT(TIMEVALUE(A2), "HHMM") to get "0600".
  • If A2 holds a text time without AM/PM (rare, but it happens), use =TEXT(TIMEVALUE(A2&" "&IF;(HOUR(TIMEVALUE(A2&":00"))<12,"AM","PM")), "HHMM") — a longer build, but it leaves nothing to guess.
  • If A2 holds a number that is already a fraction of a day, =TEXT(A2, "HHMM") works the same way as for a real time.

For a fuller walk-through of converting a column of military times into minute totals — useful when you bill by the quarter-hour — see how to convert military time to minutes in Excel.

Compare the Excel Methods Side by Side

The table below matches the storage type to the right formula so you can pick the right one without having to re-read the steps. The result column shows what each one produces for the same input, which is the same answer three different ways.

Stored asFormulaDisplay for 1800Best when
Number 1800=TEXT(TIME(INT(A2/100), MOD(A2,100), 0), "h:mm AM/PM")6:00 PMNumeric exports from payroll, IoT logs, or databases
Text "1800"=TEXT(TIMEVALUE(LEFT(A2,2)&":"&RIGHT;(A2,2)), "h:mm AM/PM")6:00 PMImported CSV where military time arrived as a string
Real time 18:00:00No formula — just format the cell as h:mm AM/PM6:00 PMAlready-parsed 24-hour time values
Any of the above, going the other way=TEXT(A2, "HHMM") or =TEXT(TIMEVALUE(A2), "HHMM")1800Producing a four-digit military string for export

Pick the row that matches how your data actually lives in the cell. Mixing them up — for example, applying the text formula to a numeric cell — gives a #VALUE! error rather than a wrong answer, which is the easiest mistake to spot.

Handle the Edge Cases: Midnight, Noon and Invalid Times

Most of the formulas above look like they should work for every hour, and they do — except for the two hours that break the pattern: 0000 and 1200. These are the ones that silently produce wrong results if the formula is not using a real TIME() call and a proper 12-hour format.

  • Midnight (0000 or 00:00) must render as 12:00 AM, not 00:00 AM and not 24:00. The TIME() function returns a serial of 0 for midnight, and the h:mm AM/PM format wraps 0 to 12:00 AM correctly.
  • Noon (1200 or 12:00) must render as 12:00 PM, not 12:00 AM. This is the trap: many people subtract 12 from the hour to convert, which turns 12 into 0 and then mislabels it. Sticking with TIME() + TEXT() + format avoids both errors.
  • Times past 23:59 such as 2400 or 2500 are not valid. The Military Time Converter tool flags an impossible time instead of silently returning one. In Excel, wrap your formula in =IFERROR(TEXT(TIME(INT(A2/100), MOD(A2,100), 0), "h:mm AM/PM"), "Invalid time") to surface bad inputs the same way.
  • Empty cells and zero convert to 12:00 AM by default. Add an IF guard like =IF(A2="", "", TEXT(TIME(INT(A2/100), MOD(A2,100), 0), "h:mm AM/PM")) if that visual matters in your report.

Skip the Formula With the Military Time Converter Tool

Formulas are the right tool when you have a column of a hundred times to flip, but for a one-off check — say a flight arrives at 1845 and you want to know what that means on the clock, or an order says report at 0500 and you want to confirm what that means in the morning — opening the Military Time Converter is faster. You choose a direction, type the hour and minute (or just type the four digits), and the result appears below with both the 12-hour and 24-hour form plus the spoken version, so you also know how to announce it on a radio or in a briefing.

Three steps cover the entire conversion:

  1. Choose a direction: 12h to 24h, or 24h to 12h.
  2. Enter the time — an hour, minute and AM/PM, or a 24-hour time like 1800.
  3. Read the converted time and its spoken military form instantly below.

Everything runs in your browser with no signup and no limit, so it works on any device the spreadsheet does. The same logic the formulas apply — adding 12 to PM hours from 1 PM onward, leaving noon at 12:00, and treating midnight as 00:00 — is exactly what the tool does, which is why both give the same answer for the same input. For wider date work like reformatting the date itself, a date format converter handles that side of the spreadsheet.

For a deeper look, see Retirement Date from DOB in Excel: Formula + Quick Tool.