none
[XLSX] Cell Number Format for Revision Information RRS feed

  • Question

  • Hello,

    I am trying to figure out how Excel 2016 decides to format numbers when it is displaying tracked changes on the history sheet.  Specifically I am looking at the numFmtId of the rcc element (18.11.1.9 of Ecma Office Open XML Part 1 - Fundamentals And Markup Language Reference) and it describes it as "Zero-based index of the number format (Fmt) record used by this cell format (XF)."  Where are these number format records stored?  The values I'm seeing for numFmtId do not make sense as either an index into the number format expressions array or as an ID of one of the NumFmts in that array.  They also don't appear to match up with the predefined formats described in 18.8.30.

    Here is an example:

    Here is how the revision is displayed in Excel 2016 history sheet





    Cell Change Sheet1 B3 ($123.00) $123.00 

    In the revision log:

    <rcc rId="3" sId="1" numFmtId="11">
    <oc r="B3">
    <v>123</v>
    </oc>
    <nc r="B3">
    <v>-123</v>
    </nc>
    </rcc>

    In styles.xml:

    <numFmts count="4">
    <numFmt numFmtId="164" formatCode="0.00_);[Red]\(0.00\)"/>
    <numFmt numFmtId="165" formatCode="&quot;$&quot;#,##0.00;[Red]&quot;$&quot;#,##0.00"/>
    <numFmt numFmtId="167" formatCode="m/d/yy;@"/>
    <numFmt numFmtId="169" formatCode="[$-F400]h:mm:ss\ AM/PM"/>
    </numFmts>

    Section 18.8.30 shows that the built-in format code for 11 is:

    0.00E+00

    I can't find the way to resolve numFmtId to a format code that matches what Excel is showing.

    Thursday, February 1, 2018 8:50 PM

Answers

  • Hi Steven,

    Thanks again for providing the clarifying information. 

    Two quick notes before addressing your  Number Formatting question.
    - In case you were not aware, the ECMA-376 specification covers office versions 98 through 2007. Office versions 2010, 2013, and 2016 (and future releases) support the ISO-29500 standard and so are documented in the corresponding ISO29550 specification. They're familiar similar but as the standard evolves they will continue to diverge. 
    - Also in case you weren't aware, the Track Changes View History auto-generated worksheet has been depreciated in newer releases of Office 2016 and replaced by a View History Task Pane. 

    You are correct in your observation that Office does leverage the numFmtId attribute within the <rcc/> element, despite what Section 2.1.738 Part 4 Section 3.8.30 in [MS-OE376]: Office Implementation Information for ECMA-376 Standards Support documents, That is an error, and I have engaged with the team that owns the documentation to address the error.

    Now, as for the numFmtId and their corresponding Number Format Codes. The numFmtId does not use the documented Number Format Codes. I have pulled the supported Format Codes for EN-US for you, below. Obviously, regional date/time and currency will be different. Documenting those is beyond the resources I have immediately available, but will be included in any documentation updates.

    Here is the listing of numFmtIds and the corresponding Format Codes:

    0 = General
    1 = 0
    2 = 0.00
    3 = #,##0
    4 = #,##0.00
    5 = 0
    6 = 0
    7 = #,##0.00
    8 = #,##0.00
    9 = $#,##0 ;[Red]$(#,##0)
    10 = $#,##0 ;[Red]$(#,##0)
    11 = $#,##0.00;[Red]$(#,##0.00)
    12 = $#,##0.00;[Red]$(#,##0.00)
    13 = 0%
    14 = 0.00%
    15 = 0.00E+00
    16 = 0.00E+00
    17 = # ?/?
    18 = # ??/??
    19 = mm-dd-yy
    20 = d-mmm-yy
    21 = d-mmm
    22 = d-mmm
    23 = h:mm AM/PM
    24 = h:mm:ss AM/PM
    25 = h:mm
    26 = h:mm:ss
    27 = m/d/yy h:mm
    28 = h:mm
    29 = h:mm:s
    30 = 0
    31 = [h]:mm:ss
    32 = $#,##0 ;[Red]$(#,##0)
    33 = #,##0
    34 = $#,##0.00;[Red]$(#,##0.00)
    35 = #,##0.00
    36+ INVALID  

    Let me know if this helps address your question about the Number Formatting used in the revisions. 

    Will Gregg | open specifications

    • Marked as answer by Steven HW Friday, February 9, 2018 8:55 PM
    Friday, February 9, 2018 8:44 PM
    Moderator

All replies

  • Hello Steven,

    Thanks for posting your question regarding number formatting in Excel 2016 and the ECMA-376 OpenXML specification. We have created a support issue to track your question and a member of the Open Specifications Support team will be engaging with you to address your question.

    Sincerely

    Will Gregg | open specifications

    Thursday, February 1, 2018 11:41 PM
    Moderator
  • Hello Steven,

    Thanks for posting your question regarding number formatting in Excel 2016 and the ECMA-376 OpenXML specification. We have created a support issue to track your question and a member of the Open Specifications Support team will be engaging with you to address your question.

    Sincerely

    Will Gregg | open specifications

    Thanks Will.  Is there an ETA for when somebody will address this question?
    Wednesday, February 7, 2018 7:27 PM
  • Hi Steven,

    We're working on an answer for you currently, and should have that posted here later today. 

    Sincerely,

    Will Gregg | open specifications

    Wednesday, February 7, 2018 9:00 PM
    Moderator
  • Hi Steven,

    Thanks for your patience as your question was investigated. 

    Let me answer how to determine the current Number Format applied to a Cell, even in the case of Track Changes being turned on, First. and then, I will address your question about the numFmtId in the <rcc/> element.

    First, the current Number Formatting is located in the styles.xml, and in the scenario where Track Changes are turned on, in a revisionlog{#}.xml that corresponds to the change that applied the Number Formatting as well.

    In the styles.xml part, there are two parent elements that are needed to be examined: <cellXfs/> and <numFmts/>. 

    Within the <cellXFs/> element there will be an <xf/> element that corresponds to the Number Formatting. For example:

        <cellXfs count="2">
            <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>
            <xf numFmtId="167" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
        </cellXfs>

    Within the <numFmts/> element there will be a corresponding <numFmt/> element whose numFmtId matches the entry in the <xf/> element. For example:

        <numFmts count="1">
            <numFmt numFmtId="167" formatCode="&quot;$&quot;#,##0.0000;[Red]&quot;$&quot;#,##0.0000"/>
        </numFmts>

    In the scenario where Track Changes are turned on, in the corresponding revisionlog{#}.xml there will be an <rfmt/> element which contains a <dxf/> element with the corresponding <numFmt/> element. For example:

        <rfmt sheetId="1" sqref="A1">
            <dxf>
                <numFmt numFmtId="167" formatCode="&quot;$&quot;#,##0.0000;[Red]&quot;$&quot;#,##0.0000"/>
            </dxf>
        </rfmt>

    Now, to address your question regarding the numFmtId attribute contained within the <rcc/> element. For example:

        <rcc rId="3" sId="1" numFmtId="11">
            <oc r="A1">
                <v>-123</v>
            </oc>
            <nc r="A1">
                <v>123</v>
            </nc>
        </rcc>

    Per the Office implementation notes ([MS-OE376]: Office Implementation Information for ECMA-376 Standards Support), Section 2.1.738 Part 4 Section 3.8.30, numFmt (Number Format), The numFmtId attribute is ignored in all instances that are not contained within a <numFmts/> parent element.

    So, the numFmtId="11" you are seeing, and in my example are basically irrelevant and ignored by Office. So, you cannot use the <rcc/> element to determine the current Number Formatting that is applied to a cell. 

    The reason that the numFmtId is being written out as "11", is because outside of the <cellXfs /> and <numFmts /> elements the Numbering Format defaults to General, and any value that exceeds the limits on General is adjusted. In this instance, the Number Format is considered to be exponential. However, keep in mind that the attribute value is ignored by Office, and should not be used. 

    Let us know if that helps answer your questions.

    Sincerely,

    Will Gregg | open specifications

    Thursday, February 8, 2018 1:33 AM
    Moderator
  • Hi Steven,

    Thanks for your patience as your question was investigated. 

    Let me answer how to determine the current Number Format applied to a Cell, even in the case of Track Changes being turned on, First. and then, I will address your question about the numFmtId in the <rcc/> element.

    First, the current Number Formatting is located in the styles.xml, and in the scenario where Track Changes are turned on, in a revisionlog{#}.xml that corresponds to the change that applied the Number Formatting as well.

    In the styles.xml part, there are two parent elements that are needed to be examined: <cellXfs/> and <numFmts/>. 

    Within the <cellXFs/> element there will be an <xf/> element that corresponds to the Number Formatting. For example:

        <cellXfs count="2">
            <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>
            <xf numFmtId="167" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
        </cellXfs>

    Within the <numFmts/> element there will be a corresponding <numFmt/> element whose numFmtId matches the entry in the <xf/> element. For example:

        <numFmts count="1">
            <numFmt numFmtId="167" formatCode="&quot;$&quot;#,##0.0000;[Red]&quot;$&quot;#,##0.0000"/>
        </numFmts>

    In the scenario where Track Changes are turned on, in the corresponding revisionlog{#}.xml there will be an <rfmt/> element which contains a <dxf/> element with the corresponding <numFmt/> element. For example:

        <rfmt sheetId="1" sqref="A1">
            <dxf>
                <numFmt numFmtId="167" formatCode="&quot;$&quot;#,##0.0000;[Red]&quot;$&quot;#,##0.0000"/>
            </dxf>
        </rfmt>

    Now, to address your question regarding the numFmtId attribute contained within the <rcc/> element. For example:

        <rcc rId="3" sId="1" numFmtId="11">
            <oc r="A1">
                <v>-123</v>
            </oc>
            <nc r="A1">
                <v>123</v>
            </nc>
        </rcc>

    Per the Office implementation notes ([MS-OE376]: Office Implementation Information for ECMA-376 Standards Support), Section 2.1.738 Part 4 Section 3.8.30, numFmt (Number Format), The numFmtId attribute is ignored in all instances that are not contained within a <numFmts/> parent element.

    So, the numFmtId="11" you are seeing, and in my example are basically irrelevant and ignored by Office. So, you cannot use the <rcc/> element to determine the current Number Formatting that is applied to a cell. 

    The reason that the numFmtId is being written out as "11", is because outside of the <cellXfs /> and <numFmts /> elements the Numbering Format defaults to General, and any value that exceeds the limits on General is adjusted. In this instance, the Number Format is considered to be exponential. However, keep in mind that the attribute value is ignored by Office, and should not be used. 

    Let us know if that helps answer your questions.

    Sincerely,

    Will Gregg | open specifications

    Hi Will,

    EDIT:  Most of my screenshots are missing.  Got them in by using PNGs.

    Thanks for the answer, but I think you've missed the specifics of my question.  This is about the rcc element and how to format the numbers for display on the history tab that Excel generates when told to.  Office is 100% leveraging the numFmtId in the RCC element as I'll illustrate with the following screenshots and XML.

    First, with tracked changes turned on, I created a revision.

    Then I saved the document and told Excel to list changes on a new sheet:

    This revision was created by changing a negative number -123.456 to a positive number, 123.456.  Here is the revision element for it. 

    <rcc rId="18" sId="1" numFmtId="4">
      <oc r="B1">
        <v>-123.456</v>
      </oc>
      <nc r="B1">
        <v>123.456</v>
      </nc>
    </rcc> 

    This is how it is displayed on the revision history tab that Excel creates.  It is revision number 18:

    New Value: 123.46  Old Value: -123.46

    I then hand edited the XML of that specific revision and changed the numFmtId to 11.  Here is the resultant XML: 

    <rcc rId="18" sId="1" numFmtId="11">
         <oc r="B1">
               <v>-123.456</v>
         </oc>
         <nc r="B1">
               <v>123.456</v>
         </nc>
    </rcc>

    This is how it is now displayed on the revision history tab that Excel creates.  The revision number is unchanged, 18: 

    New Value: $123.46  Old Value: ($123.46)

    As you can see, the formatting on the history tab that Excel creates has changed.  I then hand edited the same revision again, changing the numFmtId to 15:               

    <rcc rId="18" sId="1" numFmtId="15">
      <oc r="B1">
          <v>-123.456</v>
      </oc>
      <nc r="B1">
        <v>123.456</v>
      </nc>
    </rcc>               

    And here is how Excel now displays the revision on the history tab that it has generated.  The revision number stays unchanged, 18:

    New Value: 1.23E+02  Old Value: -1.23E+02

    It is very important to note that I used the numbers 11 and 15 for the numFmtId when I edited the XML because I have seen these numbers used for the numFmtId in other documents.  I didn't want to confuse the issue by using multiple documents. 

    I have edited the XML to show that:

    1.  Excel is using the numFmtId for formatting of the number on the revision history tab it generates

    2.  The numFmtIds used in the rcc element of the revisions to format the number on the revision history tab that Excel generates are not matching up with the formatting described in 18.8.30.

    • For example, 18.8.30 dictates that when a numFmtId is 15 it is formatted as a date:  d-mmm-yy.  When the numFmtId is part of the rcc element and is set to 15, Excel formats it as an exponent.

    3.  I have not been able to find any documentation about this special case and was hoping to get more guidance on how to handle it.  As of right now I am forced to use trial and error to figure out the correct formatting to use on the history tab that Excel generated.  I believe this behavior needs to be documented.

     




    • Edited by Steven HW Thursday, February 8, 2018 3:28 PM Got SS working
    Thursday, February 8, 2018 3:12 PM
  • Hi Steven,

    Thanks for clarifying your question. I did misunderstand your intention. Thanks for the screenshots as well, those are indeed helpful.

    Let me drill into this and get back to you later today with what I find.

    Sincerely,

    Will Gregg | open specifications

    Thursday, February 8, 2018 3:37 PM
    Moderator
  • Hi Steven,

    Thanks again for providing the clarifying information. 

    Two quick notes before addressing your  Number Formatting question.
    - In case you were not aware, the ECMA-376 specification covers office versions 98 through 2007. Office versions 2010, 2013, and 2016 (and future releases) support the ISO-29500 standard and so are documented in the corresponding ISO29550 specification. They're familiar similar but as the standard evolves they will continue to diverge. 
    - Also in case you weren't aware, the Track Changes View History auto-generated worksheet has been depreciated in newer releases of Office 2016 and replaced by a View History Task Pane. 

    You are correct in your observation that Office does leverage the numFmtId attribute within the <rcc/> element, despite what Section 2.1.738 Part 4 Section 3.8.30 in [MS-OE376]: Office Implementation Information for ECMA-376 Standards Support documents, That is an error, and I have engaged with the team that owns the documentation to address the error.

    Now, as for the numFmtId and their corresponding Number Format Codes. The numFmtId does not use the documented Number Format Codes. I have pulled the supported Format Codes for EN-US for you, below. Obviously, regional date/time and currency will be different. Documenting those is beyond the resources I have immediately available, but will be included in any documentation updates.

    Here is the listing of numFmtIds and the corresponding Format Codes:

    0 = General
    1 = 0
    2 = 0.00
    3 = #,##0
    4 = #,##0.00
    5 = 0
    6 = 0
    7 = #,##0.00
    8 = #,##0.00
    9 = $#,##0 ;[Red]$(#,##0)
    10 = $#,##0 ;[Red]$(#,##0)
    11 = $#,##0.00;[Red]$(#,##0.00)
    12 = $#,##0.00;[Red]$(#,##0.00)
    13 = 0%
    14 = 0.00%
    15 = 0.00E+00
    16 = 0.00E+00
    17 = # ?/?
    18 = # ??/??
    19 = mm-dd-yy
    20 = d-mmm-yy
    21 = d-mmm
    22 = d-mmm
    23 = h:mm AM/PM
    24 = h:mm:ss AM/PM
    25 = h:mm
    26 = h:mm:ss
    27 = m/d/yy h:mm
    28 = h:mm
    29 = h:mm:s
    30 = 0
    31 = [h]:mm:ss
    32 = $#,##0 ;[Red]$(#,##0)
    33 = #,##0
    34 = $#,##0.00;[Red]$(#,##0.00)
    35 = #,##0.00
    36+ INVALID  

    Let me know if this helps address your question about the Number Formatting used in the revisions. 

    Will Gregg | open specifications

    • Marked as answer by Steven HW Friday, February 9, 2018 8:55 PM
    Friday, February 9, 2018 8:44 PM
    Moderator
  • Hi Steven,

    Thanks again for providing the clarifying information. 

    Two quick notes before addressing your  Number Formatting question.
    - In case you were not aware, the ECMA-376 specification covers office versions 98 through 2007. Office versions 2010, 2013, and 2016 (and future releases) support the ISO-29500 standard and so are documented in the corresponding ISO29550 specification. They're familiar similar but as the standard evolves they will continue to diverge. 
    - Also in case you weren't aware, the Track Changes View History auto-generated worksheet has been depreciated in newer releases of Office 2016 and replaced by a View History Task Pane. 

    You are correct in your observation that Office does leverage the numFmtId attribute within the <rcc/> element, despite what Section 2.1.738 Part 4 Section 3.8.30 in [MS-OE376]: Office Implementation Information for ECMA-376 Standards Support documents, That is an error, and I have engaged with the team that owns the documentation to address the error.

    Now, as for the numFmtId and their corresponding Number Format Codes. The numFmtId does not use the documented Number Format Codes. I have pulled the supported Format Codes for EN-US for you, below. Obviously, regional date/time and currency will be different. Documenting those is beyond the resources I have immediately available, but will be included in any documentation updates.

    Here is the listing of numFmtIds and the corresponding Format Codes:

    0 = General
    1 = 0
    2 = 0.00
    3 = #,##0
    4 = #,##0.00
    5 = 0
    6 = 0
    7 = #,##0.00
    8 = #,##0.00
    9 = $#,##0 ;[Red]$(#,##0)
    10 = $#,##0 ;[Red]$(#,##0)
    11 = $#,##0.00;[Red]$(#,##0.00)
    12 = $#,##0.00;[Red]$(#,##0.00)
    13 = 0%
    14 = 0.00%
    15 = 0.00E+00
    16 = 0.00E+00
    17 = # ?/?
    18 = # ??/??
    19 = mm-dd-yy
    20 = d-mmm-yy
    21 = d-mmm
    22 = d-mmm
    23 = h:mm AM/PM
    24 = h:mm:ss AM/PM
    25 = h:mm
    26 = h:mm:ss
    27 = m/d/yy h:mm
    28 = h:mm
    29 = h:mm:s
    30 = 0
    31 = [h]:mm:ss
    32 = $#,##0 ;[Red]$(#,##0)
    33 = #,##0
    34 = $#,##0.00;[Red]$(#,##0.00)
    35 = #,##0.00
    36+ INVALID  

    Let me know if this helps address your question about the Number Formatting used in the revisions. 

    Will Gregg | open specifications

    Yup, that's exactly what I was looking for, thanks!  And how so very strange that the rcc element just does its own thing.  Thanks very much for this.
    Friday, February 9, 2018 8:56 PM