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 Format
to the time display you desire:
Then 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 🙂
Thanks for being a fan!
Yes, I know what that time format is. I won’t spoil it for other readers. 🙂
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.
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!
Thanks for the resource Greg
Doesnt work. I typed 1600 and it returned 0000
I clicked into the cell and now it says 5/18/1904 12:00:00 AM.
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
Military time does NOT use colons. What you are showing is 24-hour clock time not military time
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.