How to Convert Military Time on an Excel Spreadsheet

by David Weedmark

Microsoft Excel 2013 can use both military and civilian time formats. Using Excel's Time format options, you can convert from one to the other for times you have entered manually or times using Excel's current time function.

Tip

  • To convert military time yourself, add 12 hours to any time after 12:59 p.m. To convert military time to civilian time, subtract 12 hours from any time that shows more than 12 hours. For example, 1:00 p.m. is 13:00 hours in military time. Midnight is 24:00 hours.

Converting Civilian and Military Times

Type any civilian time that is after 12 noon, such as 2:00 PM, in an Excel worksheet cell. Click the Home tab and select Time from the Number menu.

Click the 1:30:55 PM option.

Type the same time in another cell. Click the Home tab and this time click the small Arrow at the bottom of the ribbon's Number section to launch the Format Cells window. Click the Time option under the Number tab and and select 1:30:55 PM option. This shows civilian time with hours, minutes and seconds.

Click 13:30.

Enter the same time in a third cell. Open the Format Cells window again. From the Time options, click 13:30, which displays military time based on a 24-hour clock.

Click 13:30:55.

Enter the same time in a fourth cell. This time, select the 13:30:55 option, which displays military time with seconds, not just hours and minutes.

Converting Excel's Current Date and Time Formula

Type -NOW() in a blank cell.

Type =Now() in any blank worksheet cell. This is Excel's formula for showing the current date and time.

Press Enter to calculate the formula.

Press Enter to accept the formula you just typed. The current date and time is displayed, automatically using a military 24-hour clock.

Change military time to civilian time.

Type the =NOW() in another empty cell and press Enter. Click the Home tab, then the small Arrow in the Number section. Select Date under the Number tab. Scroll to the bottom of the options, select 3/14/12 1:30 PM and then click OK. This converts the military time format to civilian time.

About the Author

A published author and professional speaker, David Weedmark has advised businesses and governments on technology, media and marketing for more than 20 years. He has taught computer science at Algonquin College, has started three successful businesses, and has written hundreds of articles for newspapers and magazines throughout Canada and the United States.