This  explains the changes that needs to be done for displaying the  dates in different formats.

 

Excel converts Date to Julean date(Integer).

 

The Julean date(integer) stored inside Excel sheet can be mapped to various date patterns for display.

 

The following steps be done programmatically or manually.

 

Extract the excel document(zipped).

Step1:

Open styles.xml:

 Step 1A:

 

If “numFmts” node exists then add a new “numFmt” node with ur date pattern mapped to “formatCode” attribute,with a unique id (mapped to “numFmtId”) ,else add a new “numFmts” node with “numFmt”  as child nodes (as shown below).

 

For example:

   

<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/5/main">

  <numFmts count="8">

    <numFmt numFmtId="1" formatCode="[$-409]dddd\,\ mmmm\ dd\,\ yyyy"/>

    <numFmt numFmtId="2" formatCode="m/d/yy;@"/>

    <numFmt numFmtId="3" formatCode="yyyy/d/m;@"/>

    <numFmt numFmtId="4" formatCode="mmm/yy/dd;@"/>

    <numFmt numFmtId="5" formatCode="yyyy/d/m;@"/>

    <numFmt numFmtId="6" formatCode="yyyy;@"/>

    <numFmt numFmtId="7" formatCode="mmm;@"/>

    <numFmt numFmtId="8" formatCode="dd;@"/>

    . . . .

    . . . .

  </numFmts>

  <fonts count="1">. . . . . . .

       . . . . . .

       . . . . . .

       . . . . .

</styleSheet>

 

Note: Define ur own date formats(formatCode) and check with them.

Here are some documented patterns from ECMA doc.

 

 

Step 1B:

  Map the datepattern to the xf node(child nodes of cellXfs).

  Map the desired date pattern id (numFmtId) to the format node(xf)

 

<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/5/main">

  <numFmts count="8">

    <numFmt numFmtId="164" formatCode="[$-409]dddd\,\ mmmm\ dd\,\ yyyy"/>

    <numFmt numFmtId="165" formatCode="m/d/yy;@"/>

    <numFmt numFmtId="166" formatCode="yyyy/d/m;@"/>

    <numFmt numFmtId="167" formatCode="mmm/yy/dd;@"/>

    <numFmt numFmtId="168" formatCode="yyyy/d/m;@"/>

    <numFmt numFmtId="169" formatCode="yyyy;@"/>

    <numFmt numFmtId="170" formatCode="mmm;@"/>

    <numFmt numFmtId="171" formatCode="dd;@"/>

  </numFmts>

  <fonts count="1">. . . . . . .

       . . . . . .

       . . . . . .

       . . . . .

<cellXfs count="3">

<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>

<xf numFmtId="1" />

<xf numFmtId="2" />

<xf numFmtId="3" />

 

 

</cellXfs>

 

 

</styleSheet>

 

Remember the node index number for the styleformat node(“xf”)  we mapped –in our case it is the second node(node index number: 2).

 

Save the changes made to styles.xml ,so far.

 

 

 

 

Step2:

  Open Sheet1.xml with the converted Julean date integer value.

Map the style attribute(“s”) of it to the node index number of “xf” inside “styles.xml” (in our case: from step 1B ,it is 2).

 

For example:

In ur sheet1.xml

<row r="2" spans="1:8">

      <c r="A2" s="2">

        <v>36526</v>

      </c>

....

….

36526 à is the converted juleandate value

s="2" à refers to the second “xf” node in styles.xml

Changing the value of “s” to 3 or 4... will change the format of date displayed.

 

Now zip the xml files to form the excel sheet to see the formatted dates.

 

Hope this helps.

 

The above is mostly right, but I beg to differ on the numFmtId scheme. There is not an entire freedom about which numFmtId you can use since a number of these are built-in number formats that Excel understands without serializing them in the file (any consumer/implementer will have to make wild guesses, unfortunately).

The latest draft ECMA 1.4 does not list the built-in number formats. It simply mentions that there are built-in number formats. Those need special attention though, because some are locale dependent, some are not.

But in short, if the numFmtId is lower than 0xA4 (164 in decimal) then it's a built-in number format. Anything above are free to use for custom number formats. Note that there is a limit too. If I am not wrong in current and earlier Excel versions, you'll start getting error messages such as "too many custom number formats" or something like that if you have more than 512 or so. I am not sure that this limit is increased in Excel 2007 though (there is no mention of it in the Excel 12 blog : http://blogs.msdn.com/excel/archive/2005/09/26/474258.aspx). It should be easy to test it, but I haven't taken the time to do so.

Hope this helps,

 

---

diffopc+, the definitive tool for packaging hackers, http://diffopc.arstdesign.com
xlsgen, native Excel (2007) generator, http://xlsgen.arstdesign.com

Hi Stephane,

Nice observation--custom numbering limits is something that we should be aware of while programming.

Thanks

 Muthu

As i understand it the internal number formats comes in two flavours: implied and saved.

The implied formats are listed on page 2128 in part 4 of the 1.4 spec. The rest is saved in styles.xml when they are referenced in a sheet.

plz correct me if im wrong here...

Anders Rask

hi

i have tried setting the format for the date(also for number) first in the whole column then in the cells that contain data(because the fill works only if set to a cell, not the whole column(if I want the cell that contains the data to be colored),everything by code). nothing. my formatCode="dd.mm.yyyy;@" . the sheet1.xml s attribute is set to s="2"(the third node in xf) but it doesn't do anything.
the line is like this:
" xf numFmtId="2" fontId="3" fillId="3" borderId="3" xfId="0"/ "

and i don't have to set attributes such as applyNumberFormat="1", applyFont="1", ... I have tried without it and it works fine(cell color - fill, font color/size, border) except the numFmtId(this one i tried with/without applyNumberFormat="1").

does it matter what ID I set? are some of them reserved, because there is some mapping to numFmtId="49", but there is no such numfmt inside styles.xml.

thx for any help.