相同的日期格式在几个本地化 [英] Same date format over several localizations

问题描述

目前面临的困扰我的问题很多。我希望有人能帮助我。我为大公司工作，同时使用Office 2007（32位）和Office 2010（64位）。通过整个公司编写宏以兼容是我的艰巨任务（我从来没有在VBA中编程 - 实际上这个论坛帮了我很多）。我的任务是在共享Excel表中维护一个大表。有几个宏和几个用户形式。现在我将简要解释问题：
工作表包含两个日期格式的列（开始日期和关闭日期）。这两个值都被导入到用户窗体的文本框（命令按钮午餐MsCal -exported到类 - 用日期填充这些文本框）。我只需要在两列中将日期格式设置为mm / dd / yyyy，以执行过滤和其他操作。当这个值由使用不同于英国的本地化的工作人员更新时，日期输入为dd.mm.yyyy。这是根据日期不可能进行适当的过滤。我试图通过以下方式改变格式：

Currently im facing issue that troubles me a lot. I hope that somebody could help me out. I work for big company where are both Office 2007 (32bit) and Office 2010 (64 bit) used. Writing macros to be compatibile through whole company was hard task for me (I've never programmed in VBA before - actually this forum helped me a lot). My task is to maintain one big table in shared Excel sheet. There are several macros and several userforms. Now i will decsribe the problem briefly: Sheet contains two columns with date format (start date and close date). Both values are imported to column form userform's textboxes (commandbutton lunches MsCal -exported to class- which fills those textboxes with date). What I simply need is to have date format as mm/dd/yyyy in both columns in order to perform filtering and other operations. When this values are updated by worker that uses different localization than English U.S. date is entered as dd.mm.yyyy. Thats make proper filtering based on date impossible. I tried to alter formating by:

UserForm1.TextBox10.Value = Format(Calendar1.Value, "mm/dd/yyyy")

但这段代码不知何故。在一些机器上它的工作，其中一些是不工作。那是什么让我头痛。我该怎么办？有没有办法强制excel在表格中使用相同的日期格式，并忽略Windows中的本地化设置？员工不想将本地化更改为英文，因为它们被用于其格式，或者需要用于其他应用程序。有没有办法暂时改变本地化，只有当此页打开？
任何建议将被解除。
感谢提前
Peter

but this piece of code misbehave somehow. On some machines it works, on some of them it is not working. And thats what is giving me headache. How should i proceed now? Is there a way to force excel to use same date format in sheet and ignore localization settings in Windows? Employees dont want to change localization to English U.S. because they are either used to their format, or need it for other applications. Is there a way to temporarily change localization only when this sheet opens? Any advice will be apreciated. Thanks in Advance Peter

推荐答案

你最好的做法是将日期变量转换为文本

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

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

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会将此内部值格式化为在计算机中设置格式的字符串。所以，例如，如果您的计算机中有美国的日期格式，日期41188将被形成为6/10/2012。

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.

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

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.

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

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)

1. Calendar1.Value 检索日期值（ 41188 ）


2. 格式（Calendar1.Value，mm / dd / yyyy） 将日期值转换为字符串06/10/2012


3. 当使用格式化日期（STRING）时，Excel将必须解释它是什么日期。因为电脑的日期格式是德语，它会读取日：06，月：10，年份：2012。您将使用 41070 而不是 41188

1. Calendar1.Value retrieves a date value (41188)
2. Format(Calendar1.Value, "mm/dd/yyyy") transforms the date value into a string "06/10/2012"
3. 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

如果 Calendar1.Value 检索一个Date变量，并将此日期变量赋给一个日期格式列，您将会在列中获得正确的代码，您将能够正确筛选和排序日期不管列单元格中设置的日期格式或用户计算机中设置的格式。

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 直接分配到所需的单元格。如下：

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

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

您仍然可以将 Calendar1.Value 为了让用户看到他的选择，但禁用了TextBox10，所以唯一的编辑选项是日历控件。而当与日期一起工作时，istead从TextBox10中触发它，直接从 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显示到textBox中，那么请勿在Textbox中格式化日期。而是使用：

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:

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.

这篇关于相同的日期格式在几个本地化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！