Military Time

As anyone who has been in the service (or watched enough movies) knows, the military operates on the basis of a 24-hour clock, beginning at midnight (0000 hours). So, 1:00 AM is 0100 hours, 2:00 AM is 0200 hours, and so-on up until 11:00 PM which is 2300 hours.

Not only is it used in the military, this system is the most commonly used time notation in the world today, and is the international standard for noting time.

When it comes to Excel, some of us may be faced with translating Military Time to our typical U.S. Standard Time. This isn’t terribly difficult, but a couple of Cool Formulas really help out!

First of all, let’s assume you have Cell A1 populated with military time expressed in the typical 4 digits. Using the TIMEVALUE function, which very neatly converts time represented by a text string into the Decimal Number Excel can work with, we can use the following to perform our quick bit of magic:

=TIMEVALUE(LEFT(A1, 2) & “ : ” & RIGHT(A1,2)

What this formula does is to simply choose the left two digits and put a colon between them and the last two digits. Cool!

Another bit more sophisticated approach is to use an embedded TEXT function to return a formatted string, and then use TIMEVALUE as we did in the previous example:

=TIMEVALUE( TEXT(A1, “00\:00” ))

Whichever method you use, you will, of course, want to be sure and Format your result cell to Time. For instance the military notation of 2345 will result in the decimal, .98958 (approximately), which converts to 11:45 PM with the proper formatting.

You may not need to do this type of conversion soon, but when you do, You’ll Stand Out from the Rank and File!