Excel区域设置代码中的130000 [$-130000]是什么意思？

Question

请注意，我的问题与this question密切相关。然而，由于上述问题在技术上已经在评论中得到了回答，而且我仍然非常好奇地想了解这个特定的区域代码，所以我决定打开这个问题：

以下公式将日历日期转换为中国农历日期

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

$-130000是一个语言环境代码，就像this question中解释的一样

由于我在任何地方都找不到此区域设置代码，因此我的问题是：$-130000从何而来？这是Excel特定的区域设置函数吗？

Answer 1

首先，我认为这个函数在技术上是不准确的(而且会根据你的系统设置产生不正确的信息)，因为excel是一个8位数字。从技术上讲，正确的格式是...xxyyzzzz

xx = 00

yy = 13

zzzz = 0000

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

如果您不使用前两位数字，则Microsoft将确定您使用的是'00‘。这将转换为使用您的默认系统设置(见下文)。

前两个数字(xx)表示数字(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

接下来的两个数字(yy)表示日历格式(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)

最后，最后四位数字(zzzz)表示语言代码(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

如果你想自己测试我的发现，那么你可以试一下这个例子：

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

xx = 1E =简体中文1，中文小写(对我来说更容易翻译)

yy = 02 =公历(美国)

zzzz = 0404 =繁体中文

这将转化为：

二月0五二0一九

如果你把这个从中文翻译成英文，你会得到2019年2月5日。如果你还是不相信我的话你可以试试...

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

xx = 00 =默认系统设置-我的设置为英语-美国

yy = 08 =犹太日历(同样，未发布)

zzzz = 0409 =英语(美国)(或者我可以在我的PC上将其设置为0000以获得相同的结果)

这会将日期转换为犹太日历，即"Shvat 30 5779“。

资料来源：

了解有关LCID = https://msdn.microsoft.com/en-us/globalization/mt778914.aspx https://docs.microsoft.com/en-us/windows/desktop/Intl/language-identifiers的更多信息

有关LCID = https://docs.microsoft.com/en-us/windows/desktop/Intl/language-identifier-constants-and-strings的详细信息

排序ID和语言ID的库列表：https://msdn.microsoft.com/en-us/library/cc233968.aspx

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

最后(花很多时间阅读所有这些参考链接)- https://docs.microsoft.com/en-us/windows/desktop/Intl/national-language-support-constants

Answer 2

我没有足够的声誉来评论，但这里有一些额外的信息。如果设置了xx和yy的最高位，则表示接受该格式的输入。在检查前面答案中列出的表中的值之前，需要剥离它(& 0x7f)。

此外，现代Excel365现在允许此语法：[$-lg-CN,xxyy]，其中lg-CN是区域设置的语言和国家(取代zzzz)，xxyy如先前答案中所定义。不需要前导零，整个,xxyy部分是可选的。

此外，日语地区代码允许使用gannen后缀-x-gannen，这会将e格式的1值(皇帝统治的第一年)替换为元。例如：

数字格式为[$-ja-JP-x-gannen]ggg e的5/1/2019为令和 元，而不是不带gannen指示符的令和 1。