What does the 130000 in Excel locale code [$-130000] mean?

Question

Please note that my question is closely linked to this question. However, as the above question is technically already answered in the comments and I am still very curious to understand this particular locale code I have decided to open this question:

The following formula will convert calendar dates to Chinese lunar dates

=TEXT(A1,"[$-130000]d/m/yyyy")

[$-130000] is a locale code just as the ones explained in this question

As I am not able to find this locale code anywhere here is my question: Where does $-130000 come from? Is this an Excel specific locale function?

Answer 1

First off, I believe the function is technically inaccurate (and CAN produce incorrect information depending on your system settings) as excel is excepting an eight digit number. The correct format is technically... xxyyzzzz

xx = 00

yy = 13

zzzz = 0000

=Text(A1, "[$-00130000]d/m/yyyy")

If you do not use the first two digits, then Microsoft will determine that you are using '00' instead. Which this translate to using your Default System Settings (See Below).

the first two digits (xx) represent the appearance of the number (xxyyzzzz):

Hexadecimal value = Reserved Bit for Application Use (Application Specific - From what I have been reading)
00 = System Defaults (Set in Control panel)
01 = Western language
02 = Arabic Hindi
03 = Extend Arabic Hindi
04 = Sanskrit
05 = Bengali
06 = Gorumuchi
07 = Gujarati
08 = Oriya
09 = Tamil
0A = Telugu
0B = Kannada
0C = Malayalam
0D = Thai
0E = Laotian
0F = Tibetan language
10 = Burmese
11 = Ethiopian
12 = Cambodian
13 = Mongolian
1B = Japanese 1
1C = Japanese 2
1D = Japanese 3
1E = Simplified Chinese 1, Chinese lowercase
1F = Simplified Chinese 2, Chinese uppercase
20 = Simplified Chinese 3, full-width numbers
21 = Traditional Chinese 1, traditional lowercase
22 = Traditional Chinese 2, traditional uppercase
23 = Traditional Chinese 3, full-width numbers
24 = Korean 1
25 = Korean 2
26 = Korean 3
27 = Korean 4

The next two digits (yy) represents the calendar format (xxyyzzzz):

Hexadecimal value = Calendar ID / Sort ID = See Library Source Below
00 = System Defaults (Set in Control panel)
01 = Gregorian calendar (localization)
02 = Gregorian calendar (United States)
03 = Japanese calendar (and calendar)
04 = Taiwan calendar
05 = Korean calendar (Tanji)
06 = Hajj (Arab Lunar Calendar)
07 = Thai
08 = Jewish Calendar
09 = Gregorian calendar (Middle Eastern French)
11 = Lunar Calendar (Not Officially published)
12 = Lunar Calendar (Not Officially published)
13 = Lunar Calendar (Not Officially published)
0A = Gregorian calendar (Arabic)
0B = Gregorian calendar (translated English)
0E = Lunar Calendar (Not Officially published)

Lastly, the final four digits (zzzz) represent the language code (xxyyzzzz):

Hexadecimal value = Language ID Values= LCID
0000 = System Defaults (Set in Control panel) = Not certain if the "control panel" has an LCID
0401 = Arabic = 1025
0402 = Bulgarian = 1026
0403 = Catalan = 1027
0404 = traditional Chinese) = 1028
0405 = Czech = 1029
0406 = Danish = 1030
0407 = German = 1031
0408 = Greek = 1032
0409 = English (United States) = 1033
040B = Finnish = 1035
040C = French = 1036
040D = Hebrew = 1037
040E = Hungarian = 1038
040F = Icelandic = 1039
0410 = Italian = 1040
0411 = Japanese = 1041
0412 = Korean = 1042
0413 = Dutch = 1043
0414 = Norwegian (Birkmer) = 1044
0415 = Polish = 1045
0416 = Portuguese (Brazil) = 1046
0418 = Romanian = 1048
0419 = Russian = 1049
041A = Croatian = 1050
041B = Slovak = 1051
041C = Albanian = 1052
041D = Swedish = 1053
041E = Thai = 1054
041F = Turkish = 1055
0420 = Urdu = 1056
0421 = Indonesian = 1057
0422 = Ukrainian = 1058
0423 = Belarusian = 1059
0424 = Slovenian = 1060
0425 = Estonian = 1061
0426 = Latvian = 1062
0427 = Lithuanian = 1063
0428 = Tajik = 1064
0429 = Persian = 1065
042A = Vietnamese = 1066
042B = Armenian = 1067
042C = Azerbaijani (Latin) = 1068
042D = Basque = 1069
042F = Macedonian = 1071
0436 = Afrikaans = 1078
0437 = Georgian = 1079
0438 = Faroese = 1080
0439 = Hindi = 1081
043A = Maltese = 1082
043D = Yiddish = 1085
043E = Malay = 1086
043F = Kazakh = 1087
0440 = Kyrgyz = 1088
0441 = Swahili = 1089
0442 = Turkmen = 1090
0443 = Uzbek (Latin) = 1091
0444 = Proverb = 1092
0445 = Bengali = 1093
0446 = Punjabi = 1094
0447 = Gujarati = 1095
0448 = Oriya = 1096
0449 = Tamil = 1097
044A = Telugu = 1098
044B = Kannada = 1099
044C = Malayalam = 1100
044D = Assamese = 1101
044E = Marathi = 1102
044F = Sanskrit = 1103
0450 = Mongolian = 1104
0456 = Galician = 1110
0457 = Gungan = 1111
0458 = Manipur = 1112
0459 = Sindhi = 1113
045A = Syrian = 1114
045B = Sinhalese = 1115
045C = Congga = 1116
045D = Inuit = 1117
045E = Amharic = 1118
045F = Tamasic (Berber / Arab) = 1119
0460 = Kashmiri (Arabic) = 1120
0461 = Nepali = 1121
0462 = Frisian = 1122
0463 = Pashto = 1123
0464 = Filipino = 1124
0465 = Dhivehi = 1125
0466 = Kwa = 1126
0467 = Furbe = 1127
0468 = Hausa = 1128
0469 = Ibibio = 1129
046A = Yoruba = 1130
0470 = Igbo = 1136
0471 = Kanuri = 1137
0472 = Kucht = 1138
0473 = Tigrinya (Ethiopia) = 1139
0475 = Hawaiian = 1141
0476 = Latin = 1142
0477 = Somali = 1143
0478 = Proverb = 1144
0804 = Chinese (Simplified) = 2052
0807 = German (Switzerland) = 2055
0809 = English (UK) = 2057
0814 = Norwegian (Nynorsk) = 2068
0816 = Portuguese (Portugal) = 2070
081A = Serbian (Latin) = 2074
082C = Azeri (Cyrillic) = 2092
0843 = Uzbek (Cyrillic) = 2115
0873 = Tigrinya (Eritrea) = 2163
085F = Tamasic (Latin) = 2143
0C07 = German (Austria) = 3079
0C09 = English (Australia) = 3081
0C0A = Spanish = 3082
0C0C = French (Canada) = 3084
0C1A = Serbian (Cyrillic) = 3098
1009 = English (Canada) = 4105

If you want to test my findings for your self, then you could try this as an example:

=Text("02/05/2019", "[$-1E020404]mmmm dd yyyy")

xx = 1E = Simplified Chinese 1, Chinese Lowercase (it's easier to translate for me)

yy = 02 = Gregorian Calendar (United States)

zzzz = 0404 = Traditional Chinese

This will translate to:

?? ?? ????

Which if you translate this from Chinese to English, you will get February Five 2019. Or you could try this if you still don't believe me...

=Text("02/05/2019", "[$-00080409]mmmm dd yyyy")

xx = 00 = Default System Settings - Mine is set to English-US

yy = 08 = Jewish Calendar (Again, Not Published)

zzzz = 0409 = English (US) (or I could have set it to 0000 for the same result on my PC)

This will convert the date to the Jewish Calendar, which would be "Shvat 30 5779."

Sources:

Learning more about LCID = https://msdn.microsoft.com/en-us/globalization/mt778914.aspx https://docs.microsoft.com/en-us/windows/desktop/Intl/language-identifiers

More information about the LCID = https://docs.microsoft.com/en-us/windows/desktop/Intl/language-identifier-constants-and-strings

Library List of Sort IDs and Language IDs: https://msdn.microsoft.com/en-us/library/cc233968.aspx

Calendar IDs - https://docs.microsoft.com/en-us/windows/desktop/Intl/calendar-identifiers

Lastly (A lot of hours reading all of these reference links) - https://docs.microsoft.com/en-us/windows/desktop/Intl/national-language-support-constants

Answer 2

I don't have enough reputation to comment, but here is some additional information. The top bit of xx and yy, if set, mean to accept input in that format. It needs to be stripped off (& 0x7f) before checking the value in the table listed in the prior answer.

Also modern Excel 365 now allows this syntax: [$-lg-CN,xxyy] where lg-CN is the language and country of the locale (replacing zzzz), and xxyy are as defined in the prior answer. Leading zeros are not needed and the whole ,xxyy part is optional.

In addition, a gannen suffix -x-gannen is allowed on Japanese locale codes, which replaces a 1 value (first year of emperor reign) for e formats with ?. For example:

5/1/2019 with number format [$-ja-JP-x-gannen]ggg e is ?? ? instead of ?? 1 without the gannen indicator.