IBM Lotus Symphony
|
Number format codes can consist of up to three sections separated by a semicolon (;).
In a number format code with two sections, the first section applies to positive values and zero, and the second section applies to negative values.
In a number format code with three sections, the first section applies to positive values, the second section to negative values, and the third section to the value zero.
You can also assign conditions to the three sections, so that the format is only applied if a condition is met.
Use zero (0) or the number sign (#) as placeholders in your number format code to represent numbers. The (#) only displays significant digits, while the (0) displays zeroes if there are fewer digits in the number than in the number format.
Use question marks (?) to represent the number of digits to include in the numerator and the denominator of a fraction. Fractions that do not fit the pattern that you define are displayed as floating point numbers.
If a number contains more digits to the right of the decimal delimiter than there are placeholders in the format, the number is rounded accordingly. If a number contains more digits to the left of the decimal delimiter than there are placeholders in the format, the entire number is displayed. Use the following list as a guide for using placeholders when you create a number format code:
Placeholders | Explanation |
---|---|
# | Does not display extra zeros. |
0 (Zero) | Displays extra zeros if the number has less places than zeros in the format. |
Number Format | Format Code |
---|---|
3456.78 as 3456.8 | ####.# |
9.9 as 9.900 | #.000 |
13 as 13.0 and 1234.567 as 1234.57 | #.0# |
5.75 as 5 3/4 and 6.3 as 6 3/10 | # ???/??? |
.5 as 0.5 | 0.## |
Depending on your language setting, you can use a comma or a period as a thousands separator. You can also use the separator to reduce the size of the number that is displayed by a multiple of 1000.
Number Format | Format Codes |
---|---|
15000 as 15,000 | #,### |
16000 as 16 | #, |
To include text in a number format that is applied to a cell containing numbers, place a double quotation mark (") in front of and behind the text, or a backslash (\) before a single character. For example, enter #.# "meters" to display "3.5 meters" or #.# \m to display "3.5 m".
To include text in a number format that is applied to a cell that might contain text, enclose the text by double quotation marks (" "), and then add an at sign (@). For example, enter "Total for "@ to display "Total for December".
To use a character to define the width of a space in a number format, type an underscore ( _ ) followed by the character. The width of the space varies according to the width of the character that you choose. For example, _M creates a wider space than _i.
To set the color of a section of a number format code, insert one of the following color names in square brackets [ ]:
You can define a number format so that it only applies when the condition that you specify is met. Conditions are enclosed by square brackets [ ].
You can use any combination of numbers and the <, <=, >, >=, = and <> operators.
For example, if you want to apply different colors to different temperature data, enter:
[BLUE][<0]#,0 "°C";[RED][>30]#,0 "°C";[BLACK]#,0 "°C"
All temperatures below zero are blue, temperatures between 0 and 30 °C are black, and temperatures higher than 30 °C are red.
To define a number format that adds a different text to a number depending on if the number is positive, negative, or equal to zero, use the following format:
"plus" 0;"minus" 0;"null" 0
To display numbers as percentages, add the percent sign (%) to the number format.
Scientific notation lets you write very large numbers or very small fractions in a compact form. For example, in scientific notation, 650000 is written as 6.5 x 10^5, and 0.000065 as 6.5 x 10^-5. In Lotus® Symphony™, these numbers are written as 6.5E+5 and 6.5E-5, respectively. To create a number format that displays numbers using scientific notation, enter a # or 0, and then one of the following codes E-, E+, e- or e+.
The default currency format for the cells in your spreadsheet is determined by the regional setting of your operating system. If you want, you can apply a custom currency symbol to a cell. For example, enter #,##0.00 €to display 4.50 €(Euros).
You can also specify the locale setting for the currency by entering the locale code for the country after the symbol. For example, [€-407] represents Euros in Germany. To view the locale code for a country, select the country in the Language list on the Numbers tab of the Format Cells dialog.
![]() |
The format code for currencies uses the form [$xxx-nnn], where xxx is the currency symbol, and nnn the country code. Special banking symbols, such as EUR (for Euro), do not require the country code. The currency format is not dependent on the language that you select in the Language box. |
To display days, months and years, use the following number format codes.
![]() |
Not all format codes give meaningful results for all languages. |
Format | Format Code |
---|---|
Month as 3. | M |
Month as 03. | MM |
Month as Jan-Dec | MMM |
Month as January-December | MMMM |
First letter of Name of Month | MMMMM |
Day as 2 | D |
Day as 02 | DD |
Day as Sun-Sat | NN or DDD |
Day as Sunday to Saturday | NNN or DDDD |
Day followed by comma, as in "Sunday," | NNNN |
Year as 00-99 | YY |
Year as 1900-2078 | YYYY |
Calendar week | WW |
Quarterly as Q1 to Q4 | Q |
Quarterly as 1st quarter to 4th quarter | |
Era on the Japanese Gengou calendar, single character (possible values are: M, T, S, H) | G |
Era, abbreviation | GG |
Era, full name | GGG |
Number of the year within an era, without a leading zero for single-digit years | E |
Number of the year within an era, with a leading zero for single-digit years | EE or R |
Era, full name and year | RR or GGGEE |
The above listed formatting codes work with your language version of Lotus Symphony. However, when you need to switch the locale of Lotus Symphony to another locale, you will need to know the formatting codes used in that other locale.
For example, if your software is set to an English locale, and you want to format a year with four digits, you enter YYYY as a formatting code. When you switch to a German locale, you must use JJJJ instead. The following table lists only the localized differences.
Locale | Year | Month | Day | Hour | Day of Week | Era |
---|---|---|---|---|---|---|
English - en and all not listed locales |
Y | M | D | H | A | G |
German - de | J | T | ||||
Netherlands - nl | J | U | ||||
French - fr | A | J | O | |||
Italian - it | A | G | O | X | ||
Portuguese - pt | A | O | ||||
Spanish - es | A | O | ||||
Danish - da | T | |||||
Norwegian - no, nb, nn | T | |||||
Swedish - sv | T | |||||
Finnish - fi | V | K | P | T |
To enter a date in a cell, use the Gregorian calendar format. For example, in an English locale, enter 1/2/2002 for Jan 2, 2002.
All date formats are dependent on the locale that is set in File - Preferences - Symphony - Language settings - Languages. For example, if your locale is set to 'Japanese', then the Gengou calendar is used. The default date format in Lotus Symphony uses the Gregorian Calendar.
To specify a calendar format that is independent of the locale, add a modifier in front of the date format. For example, to display a date using the Jewish calendar format in a non-Hebrew locale, enter: [~jewish]DD/MM/YYYY.
Modifier | Calendar |
---|---|
[~buddhist] | Thai Buddhist Calendar |
[~gengou] | Japanese Gengou Calendar |
[~gregorian] | Gregorian Calendar |
[~hanja] or [~hanja_yoil] | Korean Calendar |
[~hijri] | Arabic Islamic Calendar, currently supported for the following locales: ar_EG, ar_LB, ar_SA, and ar_TN |
[~jewish] | Jewish Calendar |
[~ROC] | Republic Of China Calendar |
To display hours, minutes and seconds use the following number format codes.
Format | Format Code |
---|---|
Hours as 0-23 | h |
Hours as 00-23 | hh |
Minutes as 0-59 | m |
Minutes as 00-59 | mm |
Seconds as 0-59 | s |
Seconds as 00-59 | ss |
![]() |
To display seconds as fractions, add the decimal delimiter to your number format code. For example, enter hh:mm:ss.00 to display the time as "01:02:03.45". |
If a time is entered in the form 02:03.45 or 01:02:03.45 or 25:01:02, the following formats are assigned if no other time format has been specified: MM:SS.00 or [HH]:MM:SS.00 or [HH]:MM:SS
To display numbers using native number characters, use a [NatNum1], [NatNum2], ... [NatNum11] modifier at the beginning of a number format codes.
The [NatNum1] modifier always uses a one to one character mapping to convert numbers to a string that matches the native number format code of the corresponding locale. The other modifiers produce different results if they are used with different locales. A locale can be the language and the territory for which the format code is defined, or a modifier such as [$-yyy] that follows the native number modifier. In this case, yyy is the hexadecimal MS-LCID that is also used in currency format codes. For example, to display a number using Japanese short Kanji characters in an English US locale, use the following number format code:
[NatNum1][$-411]0
In the following list, the Microsoft Excel [DBNumX] modifier that corresponds to Lotus Symphony [NatNum] modifier is shown. If you want, you can use a [DBNumX] modifier instead of [NatNum] modifier for your locale. Whenever possible, Lotus Symphony internally maps [DBNumX] modifiers to [NatNumN] modifiers.
Displaying dates using [NatNum] modifiers can have a different effect than displaying other types of numbers. Such effects are indicated by 'CAL: '. For example, 'CAL: 1/4/4' indicates that the year is displayed using the [NatNum1] modifier, while the day and month are displayed using the [NatNum4] modifier. If 'CAL' is not specified, the date formats for that particular modifier are not supported.
Chinese: Chinese lower case characters; CAL: 1/7/7 [DBNum1] |
Japanese: short Kanji characters [DBNum1]; CAL: 1/4/4 [DBNum1] |
Korean: Korean lower case characters [DBNum1]; CAL: 1/7/7 [DBNum1] |
Thai: Thai characters |
Arabic: Indic characters |
Indic: Indic characters |
Hebrew: Hebrew letters |
[NatNum2] Transliteration in |
Chinese: Chinese upper case characters; CAL: 2/8/8 [DBNum2] |
Japanese: traditional Kanji characters; CAL: 2/5/5 [DBNum2] |
Korean: Korean upper case characters [DBNum2]; CAL: 2/8/8 [DBNum2] |
[NatNum3] Transliteration in |
Chinese: fullwidth Arabic digits; CAL: 3/3/3 [DBNum3] |
Japanese: fullwidth Arabic digits; CAL: 3/3/3 [DBNum3] |
Korean: fullwidth Arabic digits [DBNum3]; CAL: 3/3/3 [DBNum3] |
[NatNum4] Transliteration in |
Chinese: lower case text [DBNum1] |
Japanese: modern long Kanji text [DBNum2] |
Korean: formal lower case text |
[NatNum5] Transliteration in |
Chinese: Chinese upper case text [DBNum2] |
Japanese: traditional long Kanji text [DBNum3] |
Korean: formal upper case text |
[NatNum6] Transliteration in |
Chinese: fullwidth text [DBNum3] |
Japanese: fullwidth text |
Korean: fullwidth text |
[NatNum7] Transliteration in |
Japanese: modern short Kanji text |
Korean: informal lower case text |
[NatNum8] Transliteration in |
Japanese: traditional short Kanji text [DBNum4] |
Korean: informal upper case text |
[NatNum9] Transliteration in |
Korean: Hangul characters |
[NatNum10] Transliteration in |
Korean: formal Hangul text [DBNum4]; CAL: 9/11/11 [DBNum4] |
[NatNum11] Transliteration in |
Korean: informal Hangul text |