Date incorrectly formatted when calendar type is Japanese Emperor Reign

[adriank09]

From what I understand, this feature might not be properly supported, as shown in #36, but I'll post anyway to raise awareness. This may be a potential issue for users who use Japanese era in their dates.

Reproduction Steps

// Corresponds to 2021/03/04 in Gregorian calendar
object v = 44259; 
NumberFormat nf = new("[$]ggge\"年 \"m\"月 \"d\"日 \";@");
NumberFormat nf2 = new(@"[$-411]ge\.m\.d;@");
// Prints the output
Console.WriteLine(nf.Format(v, new System.Globalization.CultureInfo("ja-JP")));
Console.WriteLine(nf2.Format(v, new System.Globalization.CultureInfo("ja-JP")));

Expected output

令和3年3月4日 R3.3.4

Actual output

44259 44259

[clement911]

I was not familiar with the Gannen number format.

Related: https://support.microsoft.com/en-gb/office/japan-era-changes-and-gannen-c52091af-848d-481f-a861-26ae170f8dbd#:~:text=To%20apply%20the%20Gannen%20number,the%20Calendar%20type%20drop%2Ddown.

I was able to make this work in Excel by using the following format string: [$-ja-JP]ggge"年"m"月"d"日";@

I couldn't make it work in Google Sheets though.

[adriank09]

From Microsoft's Open Specifications,

f. The standard states that date format codes e and ee become yy in all locales other than JPN/CHT. In Office, date format codes e and ee become yyyy in all locales other than JPN/CHT.

Japanese and Chinese Traditional (Taiwan) dates might be affected as well since their dates can contain era.

I was able to make this work in Excel by using the following format string: [$-ja-JP]ggge"年"m"月"d"日";@

Excel showed the date correctly when I plugged that number format string, however I observed different results in both ExcelDataReader and ExcelNumberFormat.

• IExcelDataReader.GetNumberFormatIndex() returned 166
• IExcelDataReader.GetNumberFormatString() returned [$-411]ggge"年 "m"月 "d"日 ";@
• NumberFormat.Format() returned 44259

[adriank09]

[$-411] indicates a specified locale ID (in this case, Japan). A full list of locale ID can be found in the latest published document below, but one would need to convert the IDs to decimal from hex first: https://learn.microsoft.com/en-us/openspecs/windows_protocols/ms-lcid/70feba9f-294e-491e-b6eb-56532684c37f

Reference: https://stackoverflow.com/a/899290

I'm trying to come up with a potential fix based on this finding.

[adriank09]

Created a PR for this issue in my own fork, but if the repo owner is interested to take this fix then I can extend that PR into this repo too! https://github.com/adriank09/ExcelNumberFormat/pull/3