Converting excel time format, 24 to 12 hours vise versa

December 13, 2012 @ No comments yet

Microsoft Office Excel time format as the default using computer’s region and language format. So every time you work with date and time on excel the format will be same with format showed on region and language setting. If one day you bring your document to someone else computer who use the different format, it could be a problem. Say you designed a fix table which using complex formulas, like converting the date time into text and use part of it into other calculation.

Or it could be also inflict a mistaking. For example how do you read 11/12/2012. Is it 11th December 2012, or November 12th 2012?. Peoples from UK will read it as the second one but UK will use the first one. In related to time format, there are 2 format of time we know for excel which is 24 hours and 12 hours format.

excel time format from region and language setting

Changing the time format can be done in 2 methods which inflexible and flexible. Inflexible  mean changing the computer time format following the recipient format (if you want to send the copy of your xls/xlsx file to someone else).  It could be done in region and language setting and also format cells option. If using region and language setting, first you need to ask what setting was used for region and language setting on his/her computer. This way you are getting exactly the same format as the recipient has. For you who don’t know how to set the time from region and language setting, kindly follow the following steps (For windows 7).

Setting time format from region and language (Inflexible)

  1. Clisk Start, and Control Panel.
  2. Click Change keyboards or other input methods
  3. Click Formats tab.
  4. Choose recipient Language and Contry from drop down format selection, for example English (United Stated).

Apply and restart MS Excel. Excel will now using new time format.

Format cells time setting (Inflexible)

Next using format setting on cell. Simply right click on cell, click Format Cell and select Date. On drop down Local(Location) menu, select the recipient location. Now select desired time format on type format. Please note that not all Local(location) has 24 or 12 time format. For example on English(UK), there are no time format to choose, only date setting listed on type menu.

uk time format, no time format

In excel text function time format setting (Flexible Format)

Or, using flexible format. This way no mater what setting the recipient has, the excel time format will stay the same. This can be done on setting directly into the cell using TEXT format. Here’s how.

24 Hours format

Let say Cell A1 contain time function or time text value, for example 1:02 PM.

=TEXT(A1;"[hh]:mm:ss") // the result will be 13:02:00

excel time 24 hours format


12 Hours AM/PM format

=TEXT(A1:"hh:mm:ss AM/PM") // the result will be 01:02 PM

excel time 12 hour format

The “[hh]” and “AM/PM” on the above text function determining whenever the result for excel time format is a 24 hour or 12 hour. Those 2 formulas are look similar but totally different. Note that there are square brackets on the first text function which will make sure that the result will be 24 hour format. While on 12 hour format, square brackets are not used but use AM/PM instead.

M Riza

Article by M Riza

Is a blogger since 2007, founder and editor of Oa Ultimate. Working as a network administrator, computer technician, database and web maintainer | Twitter | G+

Leave a Reply


Loading Google+ Comments ...
Loading Facebook Comments ...