How-to
Military Time in Excel and Google Sheets: Formulas That Work
Excel and Google Sheets formulas for military time: convert 1830 to 6:30 PM and back, pad 3-digit times, calculate hours between military times including overnight shifts, and total decimal hours.
By Free Military Time Calculator Team ·
To convert a military time such as 1830 in Excel or Google Sheets, turn it into a time value with TIMEVALUE(TEXT(A2,"00\:00")) and format it as h:mm AM/PM. The same formulas work in both programs. Each formula below assumes the first time is in cell A2.
Military Time to Standard Time
| Goal | Formula | 1830 returns |
|---|---|---|
| Text result | =TEXT(TIMEVALUE(TEXT(A2,"00\:00")),"h:mm AM/PM") |
6:30 PM |
| Time value | =TIMEVALUE(TEXT(A2,"00\:00")) |
0.7708 (format as time) |
TEXT(A2,"00\:00") turns the number 1830 into the text 18:30, and TIMEVALUE reads that text as a time. A 3-digit entry such as 930 becomes 09:30 automatically.
Standard Time to Military Time
| Goal | Formula | 6:30 PM returns |
|---|---|---|
| Text result | =TEXT(A2,"hhmm") |
1830 |
| Keep as a time | Custom number format hhmm |
1830 |
The cell must hold a real time value. Convert text such as “6:30 PM” first with =TIMEVALUE(A2).
Hours Between Two Military Times
With clock-in in A2 and clock-out in B2 as 4-digit numbers:
=MOD(TIMEVALUE(TEXT(B2,"00\:00"))-TIMEVALUE(TEXT(A2,"00\:00")),1)*24
- 0730 to 1615 returns 8.75 hours.
- 2200 to 0630 returns 8.5 hours:
MOD(…,1)adds a day when the shift crosses midnight.
Subtract a Break
Subtract break minutes divided by 60: with a 30-minute break in C2, add -C2/60 to the end of the formula. 2200 to 0630 with a 30-minute break returns 8.
Weekly Totals and Overtime
- Total hours:
=SUM(D2:D8)over the daily hours column. - Overtime above 40:
=MAX(0,SUM(D2:D8)-40). - Regular hours:
=MIN(40,SUM(D2:D8)).
Show Hours and Minutes
To show 8.75 as 8:45, divide by 24 and use the custom format [h]:mm: =D2/24. The square brackets keep totals above 24 hours, such as 42:15 for a week.
Common Errors
- #VALUE! — the cell holds text such as “1830 hrs”; remove the letters first.
- Negative results — the shift crossed midnight; wrap the subtraction in
MOD(…,1). - 8.45 instead of 8.75 — the minutes were typed after the decimal point; use the formulas above.
For one-off answers, use the military time converter or the time card calculator.