CODEWITHSUNDEEP
excelms-officeopenxmlnumber-formattingdate-formatting
Jayant
Jayant

Reputation: 46

Number Formats for different locales in Excel (OOXML)

In the ISO/IEC 29500-1 Part 1 Section 18.8.30, the documentation presents us with the built in numFmt (number formats) used by excel whose format code is implied and not explicitly stored.

These Format codes are provided for the en-US locale.

Where can we find the format codes for rest of the locales?

For eg: numFmtId 14 - m/d/yy (in en-US locale) and - yyyy/m/d (in ja-JP locale)

This changing behaviour can be noticed with all the implied number formats in different locales. Sometimes there would be language difference, sometimes some characters are Uppercase/Lowercase difference, etc.

I am developing a parsing application which allows to import an excel file and display the information present in the file. The application is on C++ platform.

Is there a way to track all these changes and have a complete list for all the locales and the formats used?

I have already gone through the all 4 parts of the ISO/IEC 29500, MS-XLSX, MS-OI29500 documentations. It is possible that the answer might be in one of these and I might have missed it but I think that is highly unlikely.

I want to know how to format a given built in format when opened in some different locale than the original. (I create a workbook in en-US locale with a date which has numFmtId = 14. The format of the string will change if i open this workbook in ja-JP locale. I want to know how to change the format to the new locale so that the date is in the correct format according to locale. The documentations only give the formatting rules for en-US locale)

Upvotes: 0

Views: 256

Answers (1)

AnorZaken
AnorZaken

Reputation: 2124

Excel numFormatId: 14 is defined as "Short date" format, which is an OS-level setting.

For example, here is the OS settings for Swedish date and time format: Region settings screenshot, Swedish, Win11

Similarly id:15 corresponds to the "Long date" format.

There does of course exists defaults for what these should be for various languages/regions - however to get the most accurate result for any specific machine you must ask the OS.

Upvotes: 0

Related Questions