C贸digos de formatos num茅ricos

Number format codes can consist of up to four sections separated by a semicolon (;).

  1. 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.

  2. 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.

  3. You can also assign conditions to the three sections, so that the format is only applied if a condition is met.

  4. Fourth section applies if the content is not a value, but some text. Content is represented by an at sign (@).

Decimal Places and Significant Digits

Use zero (0), the number sign (#) or the question mark (?) 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. The ? works as the # but adds a space character to keep decimal alignment if there is a hidden non-significant zero.

Use question marks (?), zeroes (0) or number signs (#) 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:

Marcadores de posici贸n

Explicaci贸n

#

Does not display extra zeros.

?

Displays space characters instead of extra zeros.

O (cero)

Displays extra zeros if the number has less places than zeros in the format.


Exemplos

Formato num茅rico

C贸digo de formato

3456,78 como 3456,8

####.#

9,9 como 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 como 0,5

0.##

.5 as 0.5聽聽 (with two extra spaces at the end)

0.???


Separador de millares

Depending on your language setting, you can use a comma, a period or a blank 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 for each separator. The examples below use comma as thousands separator:

Formato num茅rico

C贸digo de formato

15000 como 15.000

#,###

16000 como 16

#,


Including Text in Number Format Codes

Text and Numbers

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". If you use space as thousands separator, you need to insert spaces between quotes in the previous examples: #.#" meters" or #.#\ \m to get the correct result.

Text and Text

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".

Espazos

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 fill free space with a given character, use an asterisk (*) followed by this character. For instance:

*\0

will display integer value (0) preceded by as many as needed backslash characters (\) to fill column width. For accounting representation, you may left align currency symbol with a format similar to:

$_-* 0.--;$-* 0.--;$_-* -

Cor

To set the color of a section of a number format code, insert one of the following color names in square brackets [ ]:

CYAN

GREEN

BLACK

BLUE

MAGENTA

RED

WHITE

YELLOW


Condici贸ns

Conditional 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.

Positive and Negative Numbers

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

Percentages, Scientific Notation and Fraction Representation

Percentages

To display numbers as percentages, add the percent sign (%) to the number format.

Scientific Notation

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 105, and 0.000065 as 6.5 x 10-5. In LibreOffice, 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+. If sign is omitted after E or e, it won't appear for positive value of exponent. To get engineering notation, enter 3 digits (0 or #) in the integer part: ###.##E+00 for instance.

Fraction Representation

To represent a value as a fraction, format consists of two or three parts: integer optional part, numerator and denominator. Integer and numerator are separated by a blank or any quoted text. Numerator and denominator are separated by a slash character. Each part can consist of a combination of #, ? and 0 as placeholders.

Denominator is calculated to get the nearest value of the fraction with respect to the number of placeholders. For example, PI value is represented as 3 16/113 with format:

# ?/???

Denominator value can also be forced to the value replacing placeholders. For example, to get PI value as a multiple of 1/16th (i.e. 50/16), use format:

?/16

Number Format Codes of Currency Formats

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.

note

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.


Date and Time Formats

warning

Microsoft Excel wrongly assumes year 1900 to be a leap year and considers the inexistent day of 1900-02-29 as valid in date calculations. Dates prior to 1900-03-01 are therefore different in Excel and Calc.


Date Formats

To display days, months and years, use the following number format codes.

warning

Non todos os c贸digos de formato dar resultados significativos para todos os idiomas.


Formato

C贸digo de formato

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 or AAA

Day as Sunday to Saturday

NNN or DDDD or AAAA

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

QQ

Era, abbreviation. 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, short format

E

Number of the year within an era, long format

EE or R

Era, full name and year

RR or GGGEE


The above listed formatting codes work with your language version of LibreOffice. However, when you need to switch the locale of LibreOffice to another locale, you need to know the formatting codes used in that other locale.

Por exemplo, se o seu programa est谩 definido para unha localidade ingl茅s, e quere formatar un ano con catro d铆xitos, entra AAAA como un c贸digo de formato. Cando cambiar a unha localidade alem谩, pode usar JJJJ vez. A t谩boa seguinte lista s贸 as diferenzas localizadas.

Configuraci贸n rexional

Ano

Mes

D铆a

Hora

D铆a da semana

脡poca

English - en

e non todo listado locales

Y

M

D

H

A

G

Alem谩n - de

J

T

Holand茅s - nl

J

U

Franc茅s - fr

A

J

O

Italiano - it

A

G

O

X

Portugu茅s - pt

A

O

Espa帽ol - es

A

O

Dinamarqu茅s - da

T

Noruegu茅s - non, nb, nn

T

Sueco - sv

T

Finland茅s - fi

V

K

P

T


Entering Dates

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 - Languages and Locales - General. For example, if your locale is set to 'Japanese', then the Gengou calendar is used. The default date format in LibreOffice 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.

The specified calendar is exported to Microsoft Excel using extended LCID. Extended LCID can also be used in the format string. It will be converted to a calendar modifier if it is supported. See Extended LCID section below.

Modificador

Calendar

[~buddhist]

Thai Buddhist Calendar

[~gengou]

Japanese Gengou Calendar

[~gregorian]

Gregorian Calendar

[~hanja] or [~hanja_yoil]

Korean Calendar

[~hijri]

Arabic Islamic Calendar

[~jewish]

Jewish Calendar

[~ROC]

Republic Of China Calendar


Time Formats

To display hours, minutes and seconds use the following number format codes:

Formato

C贸digo de formato

Hours as 0-23

H

Hours as 00-23

HH

Hours as 00 up to more than 23

[HH]

Minutes as 0-59

M

Minutes as 00-59

MM

Minutes as 00 up to more than 59

[MM]

Seconds as 0-59

S

Seconds as 00-59

SS

Seconds as 00 up to more than 59

[SS]


note

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".


Minute time formats M and MM must be used in combination with hour or second time formats to avoid confusion with month date format.

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

Displaying Numbers Using Native Characters

NatNum modifiers

To display numbers using native number characters, use a [NatNum1], [NatNum2], ..., [NatNum11] modifier at the beginning of a number format codes.

To spell out numbers in various number, currency and date formats, use a [NatNum12] modifier with the chosen arguments at the beginning of a number format code. See NatNum12 section below.

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 LibreOffice [NatNum] modifier is shown. If you want, you can use a [DBNumX] modifier instead of [NatNum] modifier for your locale. Whenever possible, LibreOffice internally maps [DBNumX] modifiers to [NatNumN] modifiers.

A exhibici贸n de datas utilizando modificadores [NatNum] pode ter un efecto diferente que o da exhibici贸n doutros tipos de n煤mero. Eses efectos ind铆canse por medio de 'CAL: '. Por exemplo, 'CAL: 1/4/4' indica que se est谩 a usar o modificador [NatNum1] para mostrar o ano e o modificador [NatNum4] para mostrar o d铆a e o mes. Cando non se especifica 'CAL', non hai soporte para o formato de data dese modificador particular.

[NatNum0]

Try to convert any native number string to ASCII Arabic digits. If already ASCII, it remains ASCII.

[NatNum1]

Transliterations

Native Number Characters

DBNumX

Formato de data

Chin茅s

Chinese lower case characters

CAL: 1/7/7 [DBNum1]

Xapon茅s

short Kanji characters

[DBNum1]

CAL: 1/4/4 [DBNum1]

Coreano

Korean lower case characters

[DBNum1]

CAL: 1/7/7 [DBNum1]

Hebreo

Hebrew characters

脕rabe

Arabic-Indic characters

Thai

Thai characters

Hindi

Indic-Devanagari characters

Odia

Odia (Oriya) characters

Marathi

Indic-Devanagari characters

Bengal铆

Bengali characters

Punjabi

Punjabi (Gurmukhi) characters

Guxarat铆

Gujarati characters

Tamil

Tamil characters

Telugu

Telugu characters

Kannada

Kannada characters

Malayalam

Malayalam characters

Laosiano

Lao characters

Tibetano

Tibetan characters

Burmese

Burmese (Myanmar) characters

Khmer

Khmer (Cambodian) characters

Mongolian

Mongolian characters

Nepal铆

Indic-Devanagari characters

Dzongkha

Tibetan characters

Farsi

East Arabic-Indic characters

Church Slavic

Cyrillic characters


[NatNum2]

Transliterations

Native Number Characters

DBNumX

Formato de data

Chin茅s

Chinese upper case characters

CAL 2/8/8 [DBNum2]

Xapon茅s

traditional Kanji characters

CAL 2/5/5 [DBNum2]

Coreano

Korean upper case characters

[DBNum2]

CAL 2/8/8 [DBNum2]

Hebreo

Hebrew numbering


[NatNum3]

Transliterations

Native Number Characters

DBNumX

Formato de data

Chin茅s

fullwidth Arabic digits

CAL: 3/3/3 [DBNum3]

Xapon茅s

fullwidth Arabic digits

CAL: 3/3/3 [DBNum3]

Coreano

fullwidth Arabic digits

[DBNum3]

CAL: 3/3/3 [DBNum3]


[NatNum4]

Transliterations

Native Number Characters

DBNumX

Formato de data

Chin茅s

lower case text

[DBNum1]

Xapon茅s

modern long Kanji text

[DBNum2]

Coreano

formal lower case text


[NatNum5]

Transliterations

Native Number Characters

DBNumX

Formato de data

Chin茅s

Chinese upper case text

[DBNum2]

Xapon茅s

traditional long Kanji text

[DBNum3]

Coreano

formal upper case text


[NatNum6]

Transliterations

Native Number Characters

DBNumX

Formato de data

Chin茅s

fullwidth text

[DBNum3]

Xapon茅s

fullwidth text

Coreano

fullwidth text


[NatNum7]

Transliterations

Native Number Characters

DBNumX

Formato de data

Chin茅s

short lower case text

Xapon茅s

modern short Kanji text

Coreano

informal lower case text


[NatNum8]

Transliterations

Native Number Characters

DBNumX

Formato de data

Chin茅s

short upper case text

Xapon茅s

traditional short Kanji text

[DBNum4]

Coreano

informal upper case text


[NatNum9]

Transliterations

Native Number Characters

DBNumX

Formato de data

Coreano

Hangul characters


[NatNum10]

Transliterations

Native Number Characters

DBNumX

Formato de data

Coreano

formal Hangul text

[DBNum4]

CAL 9/11/11 [DBNum4]


[NatNum11]

Transliterations

Native Number Characters

DBNumX

Formato de data

Coreano

informal Hangul text


Extended LCID

If compatible, native numbering and calendar are exported to Microsoft Excel using extended LCID. Extended LCID can also be used in string format instead of NatNum modifier.

Extended LCID consists of 8 hexadecimal digits: [$-NNCCLLLL], with 2 first digits NN for native numerals, CC for calendar and LLLL for LCID code. For instance, [$-0D0741E] will be converted to [NatNum1][$-41E][~buddhist]: Thai numerals (0D) with Buddhist calendar (07) in Thai locale (041E).

Native Numerals

Two first digits NN represents native numerals:

NN

Numeral

Representation

Compatible LCID

01

脕rabe

1234567890

todo

02

Eastern Arabic

佟佗伲伽佶佴侑侉侃贍

401

1401, 3c01, 0c01, 801, 2c01, 3401, 3001, 1001, 1801, 2001, 4001, 2801, 1c01, 3801, 2401

03

Persian

郾鄄鄢鄞鄣鄱鄯鄹酃郯

429

04

Devanagari

啷оエ啷┼オ啷ガ啷ギ啷ウ

439

44E, 461, 861

05

Bengal铆

唰оЖ唰┼И唰К唰М唰Е

445

845

06

Punxab铆

喋о┄喋┼┆喋┈喋┊喋│

446

07

Guxarat铆

喃о喃┼喃喃喃

447

08

Oriya

喹о喹┼喹喹喹

448

09

Tamil

喁о喁┼喁喁喁0

449

849

0A

Telugu

啾о报啾┼豹啾爆啾碑啾宝

44A

0B

Kannada

喑о敞喑┼唱喑超喑钞喑肠

44B

0C

Malayalam

嗟о胆嗟┼氮嗟惮嗟诞嗟郸

44C

0D

Thai

喙戉箳喙撪箶喙曕箹喙椸箻喙權箰

41E

0E

Laosiano

嗷戉粧嗷撪粩嗷曕粬嗷椸粯嗷權粣

454

0F

Tibetano

嗉∴饥嗉`激嗉ム鸡嗉о绩嗉┼紶

851

10

Burmese

醽佱亗醽冡亜醽呩亞醽囜亪醽夅亐

455

11

Tigrina

釐┽崻釐崿釐嵁釐嵃釐0

473

873

12

Khmer

釤♂煝釤a煠釤メ煢釤п煥釤┽煚

453

13

Mongolian

釥戓爳釥撫爺釥曖爾釥椺牁釥欋爯

C50

850

1B

Xapon茅s

涓浜屼笁鍥涗簲鍏竷鍏節銆

411

1C

(finanzas)

澹卞紣鍙傚洓浼嶅叚涓冨叓涔濄

1D

(fullwidth Arabic)

锛戯紥锛擄紨锛曪紪锛楋紭锛欙紣

1E

Chin茅s - simplificado

涓浜屼笁鍥涗簲鍏竷鍏節鈼

804

1004, 7804

1F

(finanzas)

澹硅窗鍙佽倖浼嶉檰鏌掓崒鐜栭浂

20

(fullwidth Arabic)

锛戯紥锛擄紨锛曪紪锛楋紭锛欙紣

21

Chin茅s - tradicional

涓浜屼笁鍥涗簲鍏竷鍏節鈼

C04

1404

22

(finanzas)

澹硅渤鍙冭倖浼嶉櫢鏌掓崒鐜栭浂

23

(fullwidth Arabic)

锛戯紥锛擄紨锛曪紪锛楋紭锛欙紣

24

Coreano

涓浜屼笁鍥涗簲铵戜竷鍏節锛

812

25

(finanzas)

澹硅渤铳洓浼嶏涓冨叓涔濓Σ

26

(fullwidth Arabic)

锛戯紥锛擄紨锛曪紪锛楋紭锛欙紣

27

Korean - Hangul

鞚检澊靷检偓鞓れ湣旃犿寯甑榿


Calendar

Two next digits CC are for calendar code. Each calendar is only valid for some LCID.

CC

Calendar

Example (YYYY-MM-DD)

Supported LCID

00

Gregorian

2016-08-31

Todo

03

Gengou

28-08-31

411 (Xapon茅s)

05

Desco帽ecido

4349-08-31

Unsupported

06 ou 17

Hijri

1437-11-28

401 (Arabic - Saudi Arabia), 1401 (Arabic - Algeria), 3c01 (Arabic - Bahrain), 0c01 (Arabic - Egypt), 801 (Arabic - Iraq), 2c01 (Arabic - Jordan), 3401 (Arabic - Kuwait), 3001 (Arabic - Lebanon), 1001 (Arabic - Libya), 1801 (Arabic - Morocco), 2001 (Arabic - Oman), 4001 (Arabic - Qatar), 2801 (Arabic - Syria), 1c01 (Arabic - Tunisia), 3801 (Arabic - U.A.E.), 2401 (Arabic - Yemen) and 429 (Farsi)

07

Buddhist

2559-08-31

454 (Lao), 41E (Thai)

08

Jewish

5776-05-27

40D (Hebrew)

10

Indian

1938-06-09

Unsupported

0E, 0F, 11, 12 or 13

Desco帽ecido

2016-07-29

Unsupported

Unsupported

Hanja

412 (Coreano)

Unsupported

ROC

0105-08-31

404 (Chinese - Taiwan)


NatNum12 modifier

To spell out numbers in various number, currency and date formats, use a [NatNum12] modifier with the chosen arguments at the beginning of a number format code.

Common NatNum12 formatting examples

Formatting code

Explicaci贸n

[NatNum12]

Spell out as cardinal number: 1 鈫 one

[NatNum12 ordinal]

Spell out as ordinal number: 1 鈫 first

[NatNum12 ordinal-number]

Spell out as ordinal indicator: 1 鈫 1st

[NatNum12 capitalize]

Spell out with capitalization, as cardinal number: 1 鈫 One

[NatNum12 upper ordinal]

Spell out in upper case, as ordinal number: 1 鈫 FIRST

[NatNum12 title]

Spell out in title case, as cardinal number: 101 鈫 Hundred One

[NatNum12 USD]

Spell out as a money amount of a given currency specified by 3-letter ISO code: 1 鈫 one U.S. dollar

[NatNum12 D=ordinal-number]D" of "MMMM

Spell out as a date in format "1st of May"

[NatNum12 YYYY=title year,D=capitalize ordinal]D" of "MMMM, YYYY

Spell out as a date in format "First of May, Nineteen Ninety-nine"

[NatNum12 MMM=upper]MMM-DD

Display upper case abbreviated month name in format "JAN-01"

[NatNum12 MMMM=lower]MMMM

Display lower case month name in format "january"


Other possible arguments: "money" before 3-letter currency codes, for example [NatNum12 capitalize money USD]0.00 will format number "1.99" as "One and 99/100 U.S. Dollars".

Error Codes

If the value is outside the limits for the selected format the following error code is shown:

#FMT

Precisamos da s煤a axuda!