24-Hour Time

24-hour time is a beneficial tool in many circumstances.  If you have ever been in the military, you know that the armed forces 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. This has the advantage of preventing ambiguity and has been the standard for centuries.

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. This is particularly true in the fields of medicine, computer support, and other disciplines.

Since occasionally we may be faced with translating Military Time to our typical U.S. Standard Time in Excel, we will take a look at how this can be easily done. 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. Concatenation can has its place!

A somewhat 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 want to be sure and Format your result cell to Time. For example, the military notation of 2345 will result in the decimal, .98958 (approximately), which converts to 11:45 PM with the proper formatting. 

Although this may seem a bit obscure, more businesses than ever are using this for their time element in databases, so familiarity with these techniques can be valuable, and You will be the one who knows how to handle this in Excel!