Export (0) Print
Expand All
Expand Minimize

2.1.704 Part 1 Section 18.8.30, numFmt (Number Format)

a. The standard defines built-in format ID 14: "mm-dd-yy"; 22: "m/d/yy h:mm"; 37: "#,##0 ;(#,##0)"; 38: "#,##0 ;[Red](#,##0)"; 39: "#,##0.00;(#,##0.00)"; 40: "#,##0.00;[Red](#,##0.00)"; 47: "mmss.0"; KOR fmt 55: "yyyy-mm-dd".

Excel defines built-in format ID 14: "m/d/yyyy"; 22: "m/d/yyyy h:mm"; 37: "#,##0_);(#,##0)"; 38: "#,##0_);[Red](#,##0)"; 39: "#,##0.00_);(#,##0.00)"; 40: "#,##0.00_);[Red](#,##0.00)"; 47: "mm:ss.0"; KOR fmt 55: "yyyy/mm/dd".

b. The standard does not define a restriction on the size or content of the formatCode attribute.

In Office, the formatCode attribute is a string specifying how to format and render the numeric value of a cell. It must be less than 255 characters in length. For a string to be considered a valid format code, it should be well-formed according to the following ABNF specification.

These definitions are for en-US locale. International consideration as specified in numFmts ("[ISO/IEC-29500-1] §18.8.31; numFmts") must be accounted for and the ABNF must be modified accordingly for specific international number formats.

In the following ABNF specification, the following set of tokens can occur 0 to unbounded times:

LITERAL-STRING

LITERAL-CHAR-REPEAT

LITERAL-CHAR-SPACE

as long as they don't break apart the following elements:

INTL*

LITERAL*

NFDateTimeToken

NFPartExponential

NFPartCond

NFPartLocaleID

NFPartColor

NFPartIntNum

NFPartStrColor

The following set of tokens must occur 0 or 1 times in each section as defined in numFmts ("[ISO/IEC-29500-1] §18.8.31; numFmts").

NFPartLocaleID

NFAbsTimeToken must occur 0 or 1 times in NFDateTime.

Also, an absolute time token must not coexist with a non-absolute equivalent token in NFDateTime.

All =

([NFPartColor] NFPartCond NFGeneral) /

NFAnyNoCond /

(NFAnyNoText ASCII-SEMICOLON NFAny) /

(NFAnyNoText ASCII-SEMICOLON NFAnyNoText ASCII-SEMICOLON NFAnyNoCond) /

(NFAnyNoText ASCII-SEMICOLON NFAnyNoText ASCII-SEMICOLON

NFAnyNoTextNoCond ASCII-SEMICOLON [NFText / NFGeneral])

NFAny =

[NFPartColor]

(

([NFPartCond] NFNumber) /

NFText /

([NFPartCond] NFFraction) /

([NFPartCond] [NFDateTime] [NFGeneral] [NFDateTime])

)

NFAnyNoText =

[NFPartColor] [NFPartCond]

(

NFNumber /

NFFraction /

([NFDateTime] [NFGeneral] [NFDateTime])

)

NFAnyNoCond =

[NFPartColor]

(

NFNumber /

NFText /

NFFraction /

([NFDateTime] [NFGeneral] [NFDateTime])

)

NFAnyNoTextNoCond =

[NFPartColor]

(

NFNumber /

NFFraction /

([NFDateTime] [NFGeneral] [NFDateTime])

)

NFGeneral = INTL-NUMFMT-GENERAL

NFNumber = NFPartNum [NFPartExponential NFPartNum] *INTL-CHAR-NUMGRP-SEP *INTL-AMPM

NFDateTimeToken = NFPartYear / NFPartMonth / NFPartDay /

NFPartHour / NFPartMinute / NFPartSecond / NFAbsTimeToken

NFAbsTimeToken = NFPartAbsHour / NFPartAbsSecond / NFPartAbsMinute

NFDateTime =

*INTL-AMPM (1*(NFDateTimeToken) *(NFDateTimeToken / NFPartSubSecond /

INTL-CHAR-DATE-SEP / INTL-CHAR-TIME-SEP / INTL-AMPM))

NFText =

(1*ASCII-COMMERCIAL-AT *(ASCII-COMMERCIAL-AT / INTL-AMPM)) /

(*(ASCII-COMMERCIAL-AT / INTL-AMPM) 1*ASCII-COMMERCIAL-AT)

NFFraction = NFPartFraction ASCII-SOLIDUS NFPartFraction [NFPartNum] *INTL-AMPM

; Definitions for partial rules used by the preceding rules.

NFPartNum =

(1*NFPartNumToken2 *(NFPartNumToken2 / ASCII-PERCENT-SIGN)) /

(*(NFPartNumToken2 / ASCII-PERCENT-SIGN) 1*NFPartNumToken2)

NFPartExponential = ASCII-CAPITAL-LETTER-E NFPartSign

NFPartYear = 2(ASCII-SMALL-LETTER-Y) / 4(ASCII-SMALL-LETTER-Y)

NFPartMonth = 1*5(ASCII-SMALL-LETTER-M)

NFPartDay = 1*4(ASCII-SMALL-LETTER-D)

NFPartHour = 1*2(ASCII-SMALL-LETTER-H)

NFPartAbsHour =

ASCII-LEFT-SQUARE-BRACKET 1*ASCII-SMALL-LETTER-H ASCII-RIGHT-SQUARE-BRACKET

NFPartMinute = 1*2(ASCII-SMALL-LETTER-M)

NFPartAbsMinute =

ASCII-LEFT-SQUARE-BRACKET 1*ASCII-SMALL-LETTER-M ASCII-RIGHT-SQUARE-BRACKET

NFPartSecond =

1*2(ASCII-SMALL-LETTER-S)

NFPartAbsSecond =

ASCII-LEFT-SQUARE-BRACKET 1*ASCII-SMALL-LETTER-S ASCII-RIGHT-SQUARE-BRACKET

NFPartSubSecond = INTL-CHAR-DECIMAL-SEP 1*3ASCII-DIGIT-ZERO

NFPartCond =

ASCII-LEFT-SQUARE-BRACKET NFPartCompOper NFPartCondNum

ASCII-RIGHT-SQUARE-BRACKET

NFPartCompOper =

(ASCII-LESS-THAN-SIGN [ASCII-EQUALS-SIGN / ASCII-GREATER-THAN-SIGN]) /

ASCII-EQUALS-SIGN /

(ASCII-GREATER-THAN-SIGN [ASCII-EQUALS-SIGN])

NFPartLocaleID =

ASCII-LEFT-SQUARE-BRACKET ASCII-DOLLAR-SIGN 1*UTF16-ANY [ASCII-HYPHEN-MINUS

3*8ASCII-DIGIT-HEXADECIMAL] ASCII-RIGHT-SQUARE-BRACKET

NFPartCondNum =

[ASCII-HYPHEN-MINUS] NFPartIntNum [INTL-CHAR-DECIMAL-SEP NFPartIntNum]

[NFPartExponential NFPartIntNum]

NFPartSign = ASCII-PLUS-SIGN / ASCII-HYPHEN-MINUS

NFPartColor =

ASCII-LEFT-SQUARE-BRACKET

INTL-COLOR / (NFPartStrColor NFPart1To56)

ASCII-RIGHT-SQUARE-BRACKET

NFPart1To56 =

NFPartNumber1To9 /

NFPartNumber1To4 ASCII-DIGIT /

ASCII-DIGIT-FIVE (ASCII-DIGIT-ZERO / NFPartNumber1To6)

NFPartIntNum = 1*ASCII-DIGIT

NFPartNumToken1 = ASCII-NUMBER-SIGN / ASCII-QUESTION-MARK /

ASCII-DIGIT-ZERO

NFPartNumToken2 = NFPartNumToken1 / INTL-CHAR-DECIMAL-SEP / INTL-CHAR-NUMGRP-SEP

NFPartFraction =

(1*NFPartIntNum *(NFPartIntNum / ASCII-PERCENT-SIGN)) /

(*(NFPartIntNum / ASCII-PERCENT-SIGN) 1*NFPartIntNum) /

(1*NFPartNumToken1 *(NFPartNumToken1 / ASCII-PERCENT-SIGN)) /

(*(NFPartNumToken1 / ASCII-PERCENT-SIGN) 1*NFPartNumToken1)

NFPartNumber1To4 =

ASCII-DIGIT-ONE /

ASCII-DIGIT-TWO /

ASCII-DIGIT-THREE /

ASCII-DIGIT-FOUR

NFPartNumber1To6 =

NFPartNumber1To4 /

ASCII-DIGIT-FIVE /

ASCII-DIGIT-SIX

NFPartNumber1To9 =

NFPartNumber1To6 /

ASCII-DIGIT-SEVEN /

ASCII-DIGIT-EIGHT /

ASCII-DIGIT-NINE

NFPartStrColor =

ASCII-CAPITAL-LETTER-C ASCII-SMALL-LETTER-O ASCII-SMALL-LETTER-L

ASCII-SMALL-LETTER-O ASCII-SMALL-LETTER-R

LITERAL-CHAR = ASCII-REVERSE-SOLIDUS UTF16-ANY

LITERAL-CHAR-REPEAT = ASCII-ASTERISK UTF16-ANY

LITERAL-STRING =

(ASCII-QUOTATION-MARK 1*UTF16-ANY-WITHOUT-QUOTE ASCII-QUOTATION-MARK) /

1*LITERAL-CHAR

UTF16-ANY-WITHOUT-QUOTE = %x0000-0021 / %x0023-FFFF

LITERAL-CHAR-SPACE = ASCII-LOW-LINE UTF16-ANY

; INTL* Rules must be validated to suit the

; Locale and/or the UI language as appropriate

; ===================================================================

INTL-CHAR-DECIMAL-SEP = ASCII-FULL-STOP

INTL-CHAR-NUMGRP-SEP = ASCII-COMMA

INTL-CHAR-DATE-SEP = ASCII-SOLIDUS

INTL-CHAR-TIME-SEP = ASCII-COLON

INTL-COLOR =

(ASCII-CAPITAL-LETTER-B ASCII-SMALL-LETTER-L ASCII-SMALL-LETTER-A

ASCII-SMALL-LETTER-C ASCII-SMALL-LETTER-K) / ; Black

(ASCII-CAPITAL-LETTER-B ASCII-SMALL-LETTER-L ASCII-SMALL-LETTER-U

ASCII-SMALL-LETTER-E) / ; Blue

(ASCII-CAPITAL-LETTER-C ASCII-SMALL-LETTER-Y ASCII-SMALL-LETTER-A

ASCII-SMALL-LETTER-N) / ;Cyan

(ASCII-CAPITAL-LETTER-G ASCII-SMALL-LETTER-R ASCII-SMALL-LETTER-E

ASCII-SMALL-LETTER-E ASCII-SMALL-LETTER-N) / ; Green

(ASCII-CAPITAL-LETTER-M ASCII-SMALL-LETTER-A ASCII-SMALL-LETTER-G

ASCII-SMALL-LETTER-E ASCII-SMALL-LETTER-N

ASCII-SMALL-LETTER-T ASCII-SMALL-LETTER-A) / ; Magenta

(ASCII-CAPITAL-LETTER-R ASCII-SMALL-LETTER-E

ASCII-SMALL-LETTER-D ) / ; Red

(ASCII-CAPITAL-LETTER-W ASCII-SMALL-LETTER-H ASCII-SMALL-LETTER-I

ASCII-SMALL-LETTER-T ASCII-SMALL-LETTER-E) / ; White

(ASCII-CAPITAL-LETTER-Y ASCII-SMALL-LETTER-E ASCII-SMALL-LETTER-L

ASCII-SMALL-LETTER-L ASCII-SMALL-LETTER-O

ASCII-SMALL-LETTER-W) ; Yellow

INTL-NUMFMT-GENERAL =

ASCII-CAPITAL-LETTER-G ASCII-SMALL-LETTER-E ASCII-SMALL-LETTER-N

ASCII-SMALL-LETTER-E ASCII-SMALL-LETTER-R ASCII-SMALL-LETTER-A

ASCII-SMALL-LETTER-L

INTL-AMPM =

(ASCII-CAPITAL-LETTER-A ASCII-CAPITAL-LETTER-M ASCII-SOLIDUS

ASCII-CAPITAL-LETTER-P ASCII-CAPITAL-LETTER-M) /

"A/P"

UTF16-ANY = %x0000-FFFF

; ===================================================================

; Derived from Unicode names of ASCII characters (%x20-7F)

; ===================================================================

ASCII-SPACE = %x20

ASCII-EXCLAMATION-MARK = %x21

ASCII-QUOTATION-MARK = %x22

ASCII-NUMBER-SIGN = %x23

ASCII-DOLLAR-SIGN = %x24

ASCII-PERCENT-SIGN = %x25

ASCII-AMPERSAND = %x26

ASCII-APOSTROPHE = %x27

ASCII-LEFT-PARENTHESIS = %x28

ASCII-RIGHT-PARENTHESIS = %x29

ASCII-ASTERISK = %x2A

ASCII-PLUS-SIGN = %x2B

ASCII-COMMA = %x2C

ASCII-HYPHEN-MINUS = %x2D

ASCII-FULL-STOP = %x2E

ASCII-SOLIDUS = %x2F

ASCII-DIGIT-ZERO = %x30

ASCII-DIGIT-ONE = %x31

ASCII-DIGIT-TWO = %x32

ASCII-DIGIT-THREE = %x33

ASCII-DIGIT-FOUR = %x34

ASCII-DIGIT-FIVE = %x35

ASCII-DIGIT-SIX = %x36

ASCII-DIGIT-SEVEN = %x37

ASCII-DIGIT-EIGHT = %x38

ASCII-DIGIT-NINE = %x39

ASCII-COLON = %x3A

ASCII-SEMICOLON = %x3B

ASCII-LESS-THAN-SIGN = %x3C

ASCII-EQUALS-SIGN = %x3D

ASCII-GREATER-THAN-SIGN = %x3E

ASCII-QUESTION-MARK = %x3F

ASCII-COMMERCIAL-AT = %x40

ASCII-CAPITAL-LETTER-A = %x41

ASCII-CAPITAL-LETTER-B = %x42

ASCII-CAPITAL-LETTER-C = %x43

ASCII-CAPITAL-LETTER-D = %x44

ASCII-CAPITAL-LETTER-E = %x45

ASCII-CAPITAL-LETTER-F = %x46

ASCII-CAPITAL-LETTER-G = %x47

ASCII-CAPITAL-LETTER-H = %x48

ASCII-CAPITAL-LETTER-I = %x49

ASCII-CAPITAL-LETTER-J = %x4A

ASCII-CAPITAL-LETTER-K = %x4B

ASCII-CAPITAL-LETTER-L = %x4C

ASCII-CAPITAL-LETTER-M = %x4D

ASCII-CAPITAL-LETTER-N = %x4E

ASCII-CAPITAL-LETTER-O = %x4F

ASCII-CAPITAL-LETTER-P = %x50

ASCII-CAPITAL-LETTER-Q = %x51

ASCII-CAPITAL-LETTER-R = %x52

ASCII-CAPITAL-LETTER-S = %x53

ASCII-CAPITAL-LETTER-T = %x54

ASCII-CAPITAL-LETTER-U = %x55

ASCII-CAPITAL-LETTER-V = %x56

ASCII-CAPITAL-LETTER-W = %x57

ASCII-CAPITAL-LETTER-X = %x58

ASCII-CAPITAL-LETTER-Y = %x59

ASCII-CAPITAL-LETTER-Z = %x5A

ASCII-LEFT-SQUARE-BRACKET = %x5B

ASCII-REVERSE-SOLIDUS = %x5C

ASCII-RIGHT-SQUARE-BRACKET = %x5D

ASCII-CIRCUMFLEX-ACCENT = %x5E

ASCII-LOW-LINE = %x5F

ASCII-GRAVE-ACCENT = %x60

ASCII-SMALL-LETTER-A = %x61

ASCII-SMALL-LETTER-B = %x62

ASCII-SMALL-LETTER-C = %x63

ASCII-SMALL-LETTER-D = %x64

ASCII-SMALL-LETTER-E = %x65

ASCII-SMALL-LETTER-F = %x66

ASCII-SMALL-LETTER-G = %x67

ASCII-SMALL-LETTER-H = %x68

ASCII-SMALL-LETTER-I = %x69

ASCII-SMALL-LETTER-J = %x6A

ASCII-SMALL-LETTER-K = %x6B

ASCII-SMALL-LETTER-L = %x6C

ASCII-SMALL-LETTER-M = %x6D

ASCII-SMALL-LETTER-N = %x6E

ASCII-SMALL-LETTER-O = %x6F

ASCII-SMALL-LETTER-P = %x70

ASCII-SMALL-LETTER-Q = %x71

ASCII-SMALL-LETTER-R = %x72

ASCII-SMALL-LETTER-S = %x73

ASCII-SMALL-LETTER-T = %x74

ASCII-SMALL-LETTER-U = %x75

ASCII-SMALL-LETTER-V = %x76

ASCII-SMALL-LETTER-W = %x77

ASCII-SMALL-LETTER-X = %x78

ASCII-SMALL-LETTER-Y = %x79

ASCII-SMALL-LETTER-Z = %x7A

ASCII-LEFT-CURLY-BRACKET = %x7B

ASCII-VERTICAL-LINE = %x7C

ASCII-RIGHT-CURLY-BRACKET = %x7D

ASCII-TILDE = %x7E

ASCII-DELETE = %x7F

ASCII-CRLF = %x0d.0a

ASCII-DIGIT =

ASCII-DIGIT-ZERO /

ASCII-DIGIT-ONE /

ASCII-DIGIT-TWO /

ASCII-DIGIT-THREE /

ASCII-DIGIT-FOUR /

ASCII-DIGIT-FIVE /

ASCII-DIGIT-SIX /

ASCII-DIGIT-SEVEN /

ASCII-DIGIT-EIGHT /

ASCII-DIGIT-NINE

ASCII-DIGIT-HEXADECIMAL =

ASCII-DIGIT /

ASCII-SMALL-LETTER-A /

ASCII-SMALL-LETTER-B /

ASCII-SMALL-LETTER-C /

ASCII-SMALL-LETTER-D /

ASCII-SMALL-LETTER-E /

ASCII-SMALL-LETTER-F /

ASCII-CAPITAL-LETTER-A /

ASCII-CAPITAL-LETTER-B /

ASCII-CAPITAL-LETTER-C /

ASCII-CAPITAL-LETTER-D /

ASCII-CAPITAL-LETTER-E /

ASCII-CAPITAL-LETTER-F

; ===================================================================

c. The standard does not restrict the parent of numFmt, such that the numFmtId attribute specifies the Id used by the master style records (xf's) to reference this number format.

In Office, the numFmtId attribute specifies the Id used by the master style records (xf's) to reference this number format only if the parent element is present within numFmts element. Its value is ignored otherwise.

Show:
© 2014 Microsoft