2.1.739 Part 4 Section 3.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 (“[ECMA-376] Part 4 §3.8.31; numFmts (Number Formats)”) 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 (“[ECMA-376] Part 4 §3.8.31; numFmts (Number Formats)”).
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.