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.
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).
Apply and restart MS Excel. Excel will now using new time format.
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.
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.
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
=TEXT(A1:"hh:mm:ss AM/PM") // the result will be 01:02 PM
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.