Formatting worksheets > Number format > Structure of a custom number format

When you create a new number format (as described in the previous section), you have to enter a text string containing format codes. These codes specify how numbers are displayed.

An example:

#.00

These codes will cause numbers to be displayed as follows: All digits to the left of the decimal point, then a decimal point, then exactly two digits to the right of the decimal point.

The following format codes are available:

Format codes for numbers and text

To format numbers and text, you can use the following format codes:

Code	Result
#	Display a single digit of the number. Display nothing if there is no digit at this position of the number.
	Example: #.## applied to the number 1.2 returns 1.2.
0	Display a single digit of the number. Display a zero if there is no digit at this position.
	Example: #.00 applied to the number 1.2 returns 1.20.
?	Display a single digit of the number. Display a space if there is no digit at this position.
	Example: #.?? applied to the number 1.2 returns 1.2 (with a space behind the 2).
	Important: If at least one of the above three codes is located to the left of the decimal point, all digits to the left of the decimal point will be displayed. Example: 0.00 applied to the number 123.456 will not return 3.46, but 123.46.
. (period)	Display a decimal separator at this position.
	Note: If your system uses a different character (for example, a comma) as a decimal separator instead of a period, please use this character instead of a period.
, (comma)	Display the number with a thousands separator. This code can be placed at an arbitrary position in the format string: ,0.00 applied to the number 5000000 returns 5,000,000.00.
	Note: If your system uses a thousands separator other than a comma, use the corresponding character instead of a comma.
%	Display a percent sign at this position and multiply the number by 100.
	Example: 0% applied to the number 0.5 returns 50%.
E+ or e+	Display the number in scientific notation.
	Example: 0E+00 applied to the number 50000 returns 5E+04.
	Tip: Engineering notation (using only exponents that can be divided by 3) is also available. For example, #00E+00 applied to the number 50000 returns 50E+03 rather than 5E+04.
E- or e-	Same as E+, except that the exponent's sign is only displayed if it is negative. (It is always displayed for E+.)
	Example 1: 0E-00 applied to the number 50000 returns 5E04.
	Example 2: 0E-00 applied to the number 0.0005 returns 5E-04.
General	Wildcard for the entire cell content (formatted with the "Default" number format)
@	Wildcard for the entire cell content as text
\	Do not try to interpret the following character, just display it.
	Example: To output a # character, use the code \# (since the # sign is a valid format code).
"Text"	Do not interpret the text between the quotation marks as format code, just display it.
	Example: "MyText"
* (asterisk)	Repeat the following character. For example, *x fills the entire cell with the character "x".
	Example: The format code "Total:"* 0.00 displays the text "Total:" at the left of the cell and the number (with two decimal places) at the right. The space in between is filled with blanks.
_ (underscore)	Display a space that is as wide as the following character.
	Example: _- returns a blank space that is exactly as wide as a minus sign.
[Red]	Display the cell content in red. Other color codes which are available:
	[Black] [White] [Red] [Green] [Blue] [Cyan] [Magenta] [Yellow]

Format codes for dates and times

For dates/times, only the following format codes are valid:

Note: The format codes listed below are case-sensitive!

Code	Result
D	Day (1-31)
DD	Day, with 2 digits (01-31)
DDD	Name of day, abbreviated ("Sun" to "Mon")
DDDD	Name of day ("Sunday" to "Monday")
M	Month (1-12)
MM	Month, with 2 digits (01-12)
MMM	Name of month, abbreviated ("Jan" to "Dec")
MMMM	Name of month ("January" to "December")
MMMMM	Name of month, first letter only ("J" for January to "D" for December)
YY	Year, with 2 digits (e.g., 18)
YYYY	Year, with 4 digits (e.g., 2018)
Q	Quarter (1-4)
QQ	The word "quarter" in the language that is set in the system’s regional settings
h	Hour (0-23)
hh	Hour, with 2 digits (00-23)
m	Minute (0-59)
mm	Minute, with 2 digits (00-59)
s	Second (0-59)
ss	Second, with 2 digits (00-59)
0	Tenths of a second (for example, hh:mm:ss.0)
00	Hundredths of a second (for example, hh:mm:ss.00)
000	Thousandths of a second (for example, hh:mm:ss.000)
AM/PM	Display times in 12-hour format (with AM or PM). This has to be placed at the end of the format codes!
	Example: h:mm:ss AM/PM
am/pm	Same as AM/PM, but with am or pm (lowercase)
a/p	Same as AM/PM, but with a or p
[h]	Number of hours in "endless" time format*
[m]	Number of minutes in "endless" time format*
[mm]	Same as [m], but with 2 digits*
[s]	Number of seconds in "endless" time format*
[ss]	Same as [m], but with 2 digits*
[$-n]	Optional: This lets you specify the country code of the language to be used for day and month names. If you do not specify a country code, PlanMaker automatically uses the system's default language. Example: [$-409] represents English (US).

The "endless" time format is characterized by the fact that it does not reset times to 0:00 after 24 hours. This is particularly useful for calculations with times. For example, when you add 20:00 and 5:00, the result would normally be 1:00. If you format this calculation as [h]:mm, however, the result will be 25:00.

The endless time format also supports negative time values. For example, 7:00 - 10:00 does not result in 21:00, but in -3:00.

The same applies to minutes and seconds, which are also not reset after 60 minutes/seconds. Thus, you can even use the endless time format to convert times to minutes or seconds. For example, when you format 02:00:00 as [mm]:ss, 120:00 will be displayed, as there are 120 minutes in 2 hours. When you format it as [s], 7200 (the number of seconds) will be displayed.

Defining separate sections for positive numbers, negative numbers, zero and text

If required, you can split the text string with the format codes into up to four sections (separated by semicolons). This allows you to define your own number formats for positive numbers, negative numbers, the number zero and text – as follows:

Positive; Negative; Zero; Text

The number of sections that you include has the following effect:

Number of sections	Effect
Only 1 section:	The format codes apply to all values.
2 sections:	The codes in the 1st section apply to positive values. The codes in the 2nd section apply to negative values.
3 sections:	The codes in the 1st section apply to positive values. The codes in the 2nd section apply to negative values. The codes in the 3rd section apply to the number zero.
4 sections:	The codes in the 1st section apply to positive values. The codes in the 2nd section apply to negative values. The codes in the 3rd section apply to the number zero. The codes in the 4th section apply to text.

Example (with two sections):

0.00; -0.00[Red]

This number format has the following effect:

1st section: 0.00 causes positive numbers to be displayed with two decimal places.

2nd section: -0.00[Red] causes negative numbers to also be displayed with two decimal places. A minus sign is displayed in front of the number (see note!). The number is also colored in red.

Note: If you define a number format with two (or more) sections and you want negative values to be displayed with a minus sign, you must include a minus sign in the section for negative numbers.

For number formats with only one section, the minus signs are automatically displayed for negative numbers, even without explicitly defining them in the number format.

Defining separate sections using conditions

Alternatively, you can split the text string with the format codes into multiple sections by specifying a condition for each section.

The individual sections must by separated by semicolons. To define the conditions, simply add the desired comparison operation enclosed within square brackets to each section – for example, conditions such as [>0] or [>=100] or [=42].

You can define up to two sections with conditions. After these sections, you should add an additional section without a condition, which will then define the number format for those values that do not meet any of the conditions.

An example:

[<=0][Blue]0.00; [>30][Red]0.00; 0.00

These format codes display all values smaller than or equal to zero in blue – and values larger than 30 are displayed in red. If neither of the two conditions applies, the value is displayed in black.

Tip: "Conditional formatting" provides a much more convenient and powerful method for performing such tasks. For more information, see Conditional formatting.