# How to convert military time to standard time in Excel

Split the number into hours and minutes: `=INT(A2/100)/24+MOD(A2,100)/1440`, then format as h:mm AM/PM.

**Excel:** `=INT(A2/100)/24+MOD(A2,100)/1440` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Departs (24h) | Time |
| 1430 |  |
| 815 |  |
| 2345 |  |
| 5 |  |
| 1200 |  |

Formula in B2, filled to B6: `=INT(A2/100)/24+MOD(A2,100)/1440`

Result: 2:30 PM; 8:15 AM; 11:45 PM; 12:05 AM; 12:00 PM

## How it works

`INT(A2/100)` is the hours and `MOD(A2,100)` the minutes of a time written as a number like 1430. Dividing them by 24 and 1,440 turns them into a fraction of a day, which the h:mm AM/PM format shows as 2:30 PM. `TIME(INT(A2/100),MOD(A2,100),0)` does the same.

## Other ways

- `=TIMEVALUE(LEFT(TEXT(A2,"0000"),2)&":"&RIGHT(A2,2))`: Pads to four digits, puts a colon in and converts the text.

## Common mistakes

- Times before 10 AM lose their leading zero (0815 is stored as 815). INT and MOD handle that; LEFT on the unpadded number doesn't.
- Military time stored as text with a colon, like "14:30", is already a time; use TIMEVALUE on it.

## Questions

**How do I convert standard time to military time?**

`=TEXT(B2,"hhmm")` gives "1430" as text.

**How do I show 24-hour time without AM/PM?**

Format the cell as `hh:mm`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/convert-military-time-to-standard-time