Get the correct FormatCode for numFmtID in Excel

Question

I am analyzing the syles.xml in .XLSX file and find for the same format ID = 14, in document https://xlsxwriter.readthedocs.org/en/latest/format.html#format-set-num-format , it is "m/d/yy", but in OOXML Specification ECMA-376 1-st Edition Part 3, 147 page. , it becomes "mm-dd-yy" and when I try to set a cell to numberformat ID = 14 in Excel 2003 Chinese version, then actually the displayed string is in "yyyy-mm-dd”. Since FormatCode string varies when the locale and version of Excel varies, how can I obtain the correct FormatCode string based on the given numFmtID for the Excel installed on the local version? Are there any way to call Automation to obtain this?

Thanks

Alan

The International property of the Application object can return information about the date format on the local version. Would that help? There are also some MSDN articles about this issue, specifically referring to numfmtid 14 — Ron Rosenfeld

skkakkar skkakkar · Accepted Answer · 2015-08-07T17:18:37

Your observation is right. There is incompatibility between XML and Excel specifically for number 14. I am referring to 2 threads below wherein the efforts put by their team elaborates on this matter. Number formats are defined up-to 49 and custom format workout has been suggested to use a number more than 50. Please go through these links, if not solution of your problem they at-least vindicates your stand. Please note I have recently joined Stackoverflow and my knowledge is at beginner level. Thanks

(Info)Setting different date formats in Excel

XLSX numFmtId predefined ID = 14 doesn't match

Get the correct FormatCode for numFmtID in Excel

1 Answers