多个本地化的日期格式相同-Samedateformatoverseverallocalizations

1  

The best you can do is NEVER transform a date variable into Text.

您可以做的最好的事情是永远不要将日期变量转换为文本。

Internally for excel a date is just a consecutive number (Left from the decimal separator are days and right from the decimal separator are hours). So, for example, the 10th of June 2012, for excel is 41188. This date value is independent of the date format set on your computer.

在excel内部，日期只是一个连续的数字（小数点分隔符的左边是天，小数点分隔符的右边是小时）。因此，例如，2012年6月10日，excel是41188.此日期值与您计算机上设置的日期格式无关。

Now when it comes to represent dates (for humans to visualize) Excel will format this internal value into a String with the format set in your computer. So, for example if you have US date format in your computer, the date 41188 will be formated as 6/10/2012.

现在，当涉及表示日期（人类可视化）时，Excel会将此内部值格式化为具有计算机中设置的格式的字符串。因此，例如，如果您的计算机中有美国日期格式，则日期41188将格式化为6/10/2012。

The big challenge with dates is to input the date in the correct format. When you input a Date as a string ("6/10/2012") then Excel will interpret it depending on the date format set on your computer. If you have US format, then it will thake the first cypher as month, the second as day and the last as the year. If you have a German format, it will read the first as day, the next as month and the last as year. So, the same input ("6/10/2012") for a US Format Excel will read 10th of june as for a German format Excel will read 6th of Oktober.

日期的最大挑战是以正确的格式输入日期。当您将日期作为字符串输入（“6/10/2012”）时，Excel将根据您计算机上设置的日期格式对其进行解释。如果你有美国格式，那么它将把第一个密码作为月份，第二个作为日期，最后一个作为年份。如果您使用的是德语格式，则会将第一个作为日期，下一个作为月份，最后一个作为年份。因此，美国格式Excel的相同输入（“6/10/2012”）将读取6月10日，德语格式Excel将读取Oktober的第6个。

In your case, you should NOT format the date inside the Textbox10. For a US format Excel there is no problem, but if you have another date format, where the first cypher is the day instead of the month, you will get the wrong values: Check this example. User inputs 10th of June in a German format Excel (dd.mm.yyyy)

在您的情况下，您不应该格式化Textbox10中的日期。对于美国格式的Excel没有问题，但如果您有另一种日期格式，第一个密码是一天而不是一个月，您将得到错误的值：检查此示例。用户输入6月10日的德语格式Excel（dd.mm.yyyy）

1. Calendar1.Value retrieves a date value (41188)
2. Calendar1.Value检索日期值（41188）
3. Format(Calendar1.Value, "mm/dd/yyyy") transforms the date value into a string "06/10/2012"
4. 格式（Calendar1.Value，“mm / dd / yyyy”）将日期值转换为字符串“06/10/2012”
5. When using the formated date (STRING), Excel will have to interpret what date it is. Because the computer date format is German, it will read Day:06, Month:10, Year:2012. You will be using day 41070 instead of 41188
6. 使用格式化日期（STRING）时，Excel必须解释它的日期。由于计算机日期格式为德语，因此将显示日期：06，月份：10，年份：2012。您将使用41070而不是41188

If Calendar1.Value retrieves a Date variable and you give this date variable into a Date formated column, you will allways get the correct dale in your column and you will be able to filter and sort dates correctly regardless of the date format set inside the Column cells or the format set in the users computer.

如果Calendar1.Value检索Date变量并将此日期变量提供给Date格式列，那么您将始终在列中获得正确的dale，并且无论列中的日期格式设置如何，您都可以正确地过滤和排序日期单元格或用户计算机中设置的格式。

Now, in your case, the best would be to assign directly the Calendar1.Value to the required cell. Something like:

现在，在您的情况下，最好的方法是将Calendar1.Value直接分配给所需的单元格。就像是：

ThisworkBook.WorkSheets("Sheet1").Range("C3").Value= Calendar1.Value

You can still asign Calendar1.Value into the TextBox10 for the user to see his selection, but disable the TextBox10 so that the only edit option is the calendar control. And when working with the date, istead of thaking it from the TextBox10, taking it directly from the Calendar1.Value .

您仍然可以将Calendar1.Value标记到TextBox10中以供用户查看其选择，但禁用TextBox10以便唯一的编辑选项是日历控件。在处理日期时，不要从TextBox10中删除它，直接从Calendar1.Value获取它。

If you still need to show the selected value from Calendar1 into a textBox then do NOT format the date in the Textbox. Instead, use:

如果仍需要将Calendar1中的选定值显示到textBox中，请不要在文本框中设置日期格式。相反，使用：

UserForm1.TextBox10.Value = Cstr(Calendar1.Value) 

This way, the user will see the date in the dateformat that he has set in his computer and to which he is used to.

这样，用户将看到他在计算机中设置的日期格式中的日期以及他习惯使用的日期。

0  

One solution is to not use the date number format but rather only use the custom format for all your cell dates where you specify "mm/dd/yyyy" as the formatting string. However, in my experience, if your computer's regional settings are set to use "mm/dd/yyyy" then if you try make a custom cell formatting with this same string excel will keep as a dater linked to the computer setting so that doesn't help you. The way I worked around this was to change the date format on my computer, then format the cells as custom "mm/dd/yyyy" and save (and then turn your computer's settings back to how they were.) Now even though excel still claims they are date cells, you'll see that changing the settings on your computer doesn't change the value in the cell.

一种解决方案是不使用日期编号格式，而是仅对您指定“mm / dd / yyyy”作为格式字符串的所有单元格日期使用自定义格式。但是，根据我的经验，如果您的计算机的区域设置设置为使用“mm / dd / yyyy”，那么如果您尝试使用相同的字符串进行自定义单元格格式化，则excel将作为与计算机设置链接的dater保持不变帮你。我解决这个问题的方法是更改​​计算机上的日期格式，然后将单元格格式化为自定义“mm / dd / yyyy”并保存（然后将计算机的设置恢复为原样。）现在即使excel仍然声称它们是日期单元格，您将看到更改计算机上的设置不会更改单元格中的值。

I guess another way is to always have a cell next to your date cell that calls the TEXT function. So if you have a date in A1 then in another cell =TEXT(A1, "mm/dd/yyyy") and only refer to this new cell. But that could make your spread sheet very messy.

我想另一种方法是在日期单元格旁边总是有一个调用TEXT函数的单元格。因此，如果您在A1中有一个日期，那么在另一个单元格中= TEXT（A1，“mm / dd / yyyy”）并且只引用这个新单元格。但这可能会使您的电子表格变得非常混乱。

I guess the best solution is to just get you IT dept to set every one in the company's date settings to use the same formats.

我想最好的解决方案就是让IT部门将公司日期设置中的每一个设置为使用相同的格式。