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 left of the decimal point, then a decimal point, then exactly two digits right of the decimal point.

In detail, the following format codes are available:

Format codes for numbers and text

For formatting numbers and text, the following format codes can be used:

Code	Result
#	Display a single digit of the number. Or display nothing in case there is no digit at this position.
	Example: #.## applied to the number 1.2 returns 1.2.
0	Display a single digit of the number. Or display a zero in case 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. Or display a blank in case there is no digit at this position.
	Example: #.?? applied to the number 1.2 returns 1.2 (with a blank behind the 2).
	Important: If any of the above codes is placed to the left of the decimal point, all digits 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 utilizes a decimal separator other than a period (e.g. a comma), use the corresponding character instead of a period.
, (comma)	Display the number with thousands separators. 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 utilizes 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 does not return 5E+04 but 50E+03.
E- or e-	Same as E+, except that the exponent's sign is only displayed if it is negative. Example 1: 0E-00 applied to the number 5000 returns 5E03.
	Example 2: 0E-00 applied to the number 0.005 returns 5E-03.
General	Placeholder for the entire cell content (formatted with the number format "Default").
@	Placeholder 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, just display it.
	Example: "MyText"
* (asterisk)	Repeat the following character. For example, *x fills the entire cell with the character "x".
	Example: The format codes "Total:"* 0.00 display 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 blank 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 color. Other color codes 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 ("Su" to "Mo")
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 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 (e.g., hh:mm:ss.0)
00	Hundredths of a second (e.g., hh:mm:ss.00)
000	Thousandths of a second (e.g., hh:mm:ss.000)
AM/PM	Display times in 12-hour format (with AM or PM). 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 (lower case)
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 [s], but with 2 digits*
[$-n]	Optional: Allows you to specify the regional code of the language to be used for day and month names. If no regional code is given, PlanMaker uses the system's default language. Example: [$-409] represents English (US).

The "endless" time format distinguishes itself from other time formats by its feature of not setting the time back to 0:00 after 24 hours. This is useful for calculations with times. For example, when you add 20:00 and 5:00, the result would normally be 1:00. But if you format this calculation as [h]:mm, 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. 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. 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

You can split the text string that defines a number format into up to four sections (separated by semicolons). This allows you to define different 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
Just 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 digits after the decimal point.

2nd section: -0.00[Red] causes negative numbers to be displayed with two digits after the decimal point as well. A minus sign is displayed in front of the number (see note!). Additionally, the number is colored in red.

Note: When 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.

The reason is that, as opposed to number formats with just one section, minus signs will not be displayed automatically for number formats with multiple sections.

Defining separate sections using conditions

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

The individual sections must by separated by semicolons. To specify the condition for a section, simply type in the desired comparison, enclosed in square brackets – for example comparisons like [>0] or [>=100] or [=42].

You can define up to two sections that contain a condition. After these sections, an additional section that defines the number format for those values that don't match the condition should be specified.

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. Values not matching any of these two criteria are not colorized; they are displayed in black.

Tip: "Conditional formatting" provides a much more convenient and powerful way to perform such tasks. See Conditional formatting.