How to Make Excel Calculate Military Time

By Aaron Parson

When you enter times in an Excel 2013 spreadsheet without including "AM" or "PM," the program automatically detects that you're using a 24-hour clock. Calculations on those times work as you'd expect, without the need to change any settings. For example, 12:30 added to 10:40 gives the result 23:10. However, if you want to display times in military fashion -- 0800 rather than 8:00 -- or to add units of time above 24 hours, you have to adjust the cell formatting.

Perform Time Calculations

Enter Your Times

i Image courtesy of Microsoft

Type each of your times in its own cell, separating hours and minutes with a colon. When you select a time, you'll see its corresponding time on a 12-hour clock displayed in the Formula Bar.

Tips

Excel usually detects when you enter a time, but if you've changed the cell formatting on the sheet previously, it might not switch to using times. Right-click the cell, click Format Cells, choose Custom and enter h:mm.

Enter a Formula

i Image courtesy of Microsoft

Start a formula in a blank cell with =. Click a cell with a time to reference it in the formula, and use operators or Excel's functions to work with the times. For example, click one time, type + and click a second time to add the times together.

Calculate Above 24 Hours

i Image courtesy of Microsoft

Excel defaults to adding hours on the clock, so 23:00 plus 1:00 results in 0:00 (midnight). To add hours together without this 24-hour limit, change the cell formatting on all cells containing times, including the formula cell. Select the cells, right-click any one and pick Format Cells. Choose Custom and enter [h]:mm. With this format, your hours won't loop to 0 after reaching 24.

Display Military Time Formatting

i Image courtesy of Microsoft

Excel normally displays a colon between hours and minutes, and won't show leading zeroes. To switch to military time formatting, write a custom cell format. Select your cells, right-click one and pick Format Cells. Select Custom and enter the format hhmm. With this format, times always appear as four digits without a colon.

Tips

Use both military time formatting and times above 24 hours with the format [hh]mm.

Warnings

When using military time formats, you still need to type the colon when entering a new time. For example, type 12:00, and Excel displays "1200." If you don't type the colon, Excel won't interpret the time correctly and the cell will read "0000."

References

Writer Bio