This Excel tutorial explains how to use Excel Custom Format in Format Cells to format date, time, number, text, conditional formatting, leading zero, etc.
You may also want to read:
What is Excel Custom Format?
Excel guesses the most appropriate format for the cells when you type something in a Cell. For example, if you type 31/1/2014, Excel guesses it would be a Date, Format is dd/m/yyyy, but it would not guess it is a Text. If you type 123, Excel guesses it is a number format. You can customize the display of the value in Format Cells, for example, change format dd/m/yyyy to dd-mm-yyyy.
While you change the display of a value, it does not change the underlying value. For example, if you change the format of number 0.3333 to 2 decimal places, it will display as 0.33, but the underlying value is still 0.3333. Similarly, if you display a date from dd/mm/yyyy as mm/yyyy, you don’t lose the day.
One common issue due to incorrect format is that a formula displays as text instead of a formula where you visually see the formula with = sign, but not the result value. This occurs when you customize a format for the Cell and then decide to change the Cell to a formula. In that case , go to Format Cells and change the format to “General”. Finally, double click on the formula Cell and then press Enter.
Apply Custom Format
Right click on the cell which you want to change formatting, then choose Format Cells. The below screenshot shows a list of formats that you can choose to display. Since most of them are self-explanatory, this tutorial will only focus on “Custom”.
Custom allows you to hard code the format you want. Custom comprises 4 sections, separated by semi-colon:
Format for positive value ; Format for negative value ; Format for zero value ; Format for text
If no semi-colon is used, the condition applies to all kind of value
If one semi-colon is used, the first part applies to positive value, second part applies to negative value
If you want to skip one section in the middle, add a semi-colon to indicate the skipped section
Custom Format of value and text
The below tables shows common notations that are to be used in formatting a value or text
Custom Format of date and time
The below table shows Time related formatting. I skipping examples because it is very simple to understand.
|m||Express month in number, from 1 to 12|
|mm||Express month in number, from 01 to 12|
|mmm||Express month in short description, from Jan to Dec|
|mmmm||Express months in long description, from January to December|
|mmmmm||Express month in the first alphabet, from J to D|
|d||Express day in number, from 1 to 31|
|dd||Express day in number, from 01 to 31|
|ddd||Express day in short description, from Sun to Sat|
|dddd||Express day in long description, from Sunday to Saturday|
|yy||Express year in two digits, from 00 to 99|
|yyyy||Express year in your digits, from 1900 to 9999|
|h||Express hour from 1 to 24. If hour exceeds 24, a day will add and hour will be subtracted by 24|
|hh||Express hour from 01 to 24. If hour exceeds 24, a day will add and hour will be subtracted by 24|
|[h]||Convert date and time to hours, allow you to display hour larager than 24|
|m||Express minute from 1 to 59. If minute exceeds 60, an hour will add and minute will be subtracted by 60|
|mm||Express minute from 01 to 59. If minute exceeds 60, an hour will add and minute will be subtracted by 60|
|[m]||Convert date and time to minute, allow you to display minute larger than 60|
|s||Express second from 1 to 59. If second exceeds 60, a minute will add and second will be subtracted by 60|
|ss||Express second from 01 to 59. If second exceeds 60, a minute will add and second will be subtracted by 60|
|[s]||Convert date and time to second allow you to display second larger than 60|
|AM/PM or a/p||Display time in AM/PM or a/p format|
Instead of directly applying formats on the cells, you can also create a formula and use Text(value, “format”). However, not all of the above notations are compatible in the Function. I don’t recommend to use Text formula because it converts the value to text.
To know which notation can be used in Text Function, click here for details.