How-to Display Military Time in an Excel Spreadsheet

A few days ago, I had a user ask me a question on the blog about how she can display military time in Excel.

It is pretty simple to display in Excel but only if you know how Excel treats time.

Excel uses number to record dates and time.

For example, 1:30 PM on May 1, 2015 = 42125.5625

The 42125 = the number of days that have passed since January 1, 1900





The 0.5625 = 1:30 PM which is a fraction of 24 hours with 0 = 12 AM and 0.5 = Noon and 0.999305555555556 = 11:59 PM

So since Time in Excel is just a number, then to show Standard time vs Military time, we simply need to change the format of that number.

To do that, Select the cells you want to display as Military time and then change the Number FormatMilitary Time Number Format Menu

to the time display you desire:

Military Time Number FormatThen your numbers will now appear in Military Format. .



But what is that one time format at the bottom?  Can anyone tell me what the time format is that I show at the end of the video?  37:30:55?  Put it in the comments below.  I want to know 🙂  I am sure Pete knows 🙂

 

Video Demonstration


Thanks for being a fan!

Steve=True









10 COMMENTS

  1. 37:30:55 format allows you to caculate times greater than a 24 hour period. If I save up all my sick day hours, I will eventually have more than 24 hours “in the bank”

    • Thanks David, the explanation is greatly appreciated. Makes sense. I searched, but couldn’t find any MS help on this format for more explanation. Thanks again.

      Steve=True

  2. Nice Thanks! I knew it had to be pretty easy. I was also searching for a way to do it with a formula. I did finally figure it out and made it publically available here: Military Time Converter (the link to download the excel is just below the first converter box. Hope it helps others who want to convert on the fly!

    • Hi Kenneth, you need to enter 16:00 with the colon after 16. It will show up as 16:00 in the cell and the formula bar you will see 4pm. hope this helps

    • Thanks Texican, that is a great point. I will create an update and post it on the site as you can make this change quickly in a Custom Number format of hhmm

      • I created the custom field “hhmm” but I still have to INPUT the time as “hh:mm”. It converts it to “hhmm” after I enter it.

LEAVE A REPLY

Please enter your comment!
Please enter your name here