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
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.
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
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
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
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.
Use both military time formatting and times above 24 hours with the format [hh]mm.
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."
Aaron Parson has been writing about electronics, software and games since 2006, contributing to several technology websites and working with NewsHour Productions. Parson holds a Bachelor of Arts from The Evergreen State College in Olympia, Wash.