vba Excel 使用具有多种不同区域格式的日期/时间。VB？公式？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7351741/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel using date/time with multiple different region formats. VB? Formulas?
提问by Kyle
I'm currently in US EST and the date format used within my company is
我目前在美国东部标准时间，我公司使用的日期格式是
Month/Day/Year Hour:Minutes:sec
月/日/年 小时：分钟：秒
I need to copy and paste dates/time extracted from other regions and they have mixed formats. Some are the same as the format I have, while others are using the UK format
我需要复制和粘贴从其他地区提取的日期/时间，并且它们具有混合格式。有的和我的格式一样，有的用的是UK格式
Day/Month/Year Hour:Min:sec
日/月/年 时：分：秒
Currently, I have to manually edit the UK format to match with the rest so that my date formulas will be not have errors. This is starting to be a problem since I am facing more than 200 records, and each record has different time.
目前，我必须手动编辑英国格式以与其余格式匹配，以便我的日期公式不会有错误。这开始成为一个问题，因为我面临着 200 多条记录，而且每条记录都有不同的时间。
Is there a code or formula I can use to solve this issue ? I understand that the datetime format can be solved with System region settings, but this does not help since, changing to either formats will still require me to manually edit those not affected..
有没有我可以用来解决这个问题的代码或公式？我知道日期时间格式可以通过系统区域设置来解决，但这无济于事，因为更改为任一格式仍然需要我手动编辑那些不受影响的..
I'm thinking of using Visual Basic to iterate through, using each row's unique ID to identify if it is UK or US. Am I on the right track ?
我正在考虑使用 Visual Basic 进行迭代，使用每一行的唯一 ID 来识别它是英国还是美国。我在正确的轨道上吗？
Otherwise is there an excel formula or method available for this?
否则是否有可用的excel公式或方法？
Thanks in advance
提前致谢
回答by MikeD
You should always aim at converting something that looks like a date into a date. A date will always remain a date and you can display it in whatever format you want. It will even survive the transition from one language to another.
您应该始终致力于将看起来像日期的内容转换为日期。日期将始终保持为日期，您可以以任何您想要的格式显示它。它甚至可以从一种语言过渡到另一种语言。
So if your source is a date you are fine. If it is a string, I would do this:
因此，如果您的消息来源是约会对象，那就没问题了。如果它是一个字符串，我会这样做：
- convert the source string using =DATEVALUE() ... this will work if the month name is in the system language
- if temporarily changing your system language is not an option, the next would be =DATE(yy,mm,dd) whereby the arguments must be created using =LEFT(...), =MID(...), =RIGHT(...)
eventually you must convert month names from one language to another like in the following example:
A1 contains
JANFEBMAR...A2 contains
J?NFEBM?R...conversion formula
=MID(A2;FIND("MAR";A1);3)
- 使用 =DATEVALUE() 转换源字符串...如果月份名称使用系统语言，这将起作用
- 如果暂时无法更改系统语言，则下一个将是 =DATE(yy,mm,dd)，其中必须使用 =LEFT(...), =MID(...), =RIGHT( ...）
最终，您必须将月份名称从一种语言转换为另一种语言，如下例所示：
A1 包含
JANFEBMAR...A2包含
J?NFEBM?R...换算公式
=MID(A2;FIND("MAR";A1);3)
Editin reply to Kyle's comment:
编辑回复凯尔的评论：
Besides the fact that the German(Austria) locale would immediately recognize this string as a date, let's split the problem into chewable pieces. Your date string is asumed in A1
除了德国（奥地利）语言环境会立即将此字符串识别为日期这一事实之外，让我们将问题分解为可咀嚼的部分。您的日期字符串假定在 A1 中
- the time part is easy: it is an additive term
TIMEVALUE(RIGHT(A1;8)) splitting the date we have to fight against seperators at dynamic locations. The positions of the delimiters can be found by
2a)
=FIND("/";A1;1)... find position of first delimiter2b)
=FIND("/";A1;4)... 2nd delimiter, asuming the first figure can be only 1 or 2 char's and no blanks before - alternatively we must replace the constant "4" by term (2a)+12c)
=FIND("/";A1;FIND("/";A1;1)+1)... safer version of (2b)now we have most things ready to construct our left's mid's and right's
3a)
=LEFT(A1;FIND("/";A1;1)-1)... 1st figure... length of (2a)-13b)
=MID(A1;FIND("/";A1;1)+1;FIND("/";A1;FIND("/";A1;1)+1)-FIND("/";A1;1)-1)... 2nd figure... start:=(2a)+1, num_Chars=(2c)-(2a)-13c)
=MID(A1;FIND("/";A1;FIND("/";A1;1)+1)+1;4)... 3rd figure... start:=(2c)+1, num_chars = 4 - asuming the year is always 4 digit. the more abstract case would be a (3b) with (2c) being a=FIND()for the first blank
- 时间部分很简单：它是一个附加项
TIMEVALUE(RIGHT(A1;8)) 拆分我们必须在动态位置与分隔符作战的日期。可以通过以下方式找到分隔符的位置
2a)
=FIND("/";A1;1)... 找到第一个分隔符的位置2b)
=FIND("/";A1;4)...第二个分隔符，假设第一个数字只能是 1 或 2 个字符并且之前没有空格 - 或者我们必须用术语 (2a)+1 替换常量“4”2c)
=FIND("/";A1;FIND("/";A1;1)+1)... (2b) 的更安全版本现在我们已经准备好构建左边的中间和右边的大部分东西
3a)
=LEFT(A1;FIND("/";A1;1)-1)...第 1 个数字... (2a)-1 的长度3b)
=MID(A1;FIND("/";A1;1)+1;FIND("/";A1;FIND("/";A1;1)+1)-FIND("/";A1;1)-1)...第二个数字... start:=(2a)+1, num_Chars=(2c)-(2a)-13c)
=MID(A1;FIND("/";A1;FIND("/";A1;1)+1)+1;4)...第三个数字... start:=(2c)+1, num_chars = 4 - 假设年份总是 4 位数字。更抽象的情况是 (3b) 与 (2c) 是=FIND()第一个空白
How to interpret especially the 1st and 2nd figure (i.e. MM/DD or DD/MM) is up to you.
如何解释尤其是第一和第二个数字（即 MM/DD 或 DD/MM）取决于您。
now comes the fun part, i.e. concatenating all these formulae into one monster to get the date
4a) start by entering =DATE(1;2;3)
4b) replace 1 by (3c), 2 by (3b), 3 by (3a) ... do not copy the leading "="
4c)
=DATE(MID(A1;FIND("/";A1;FIND("/";A1;1)+1)+1;4);MID(A1;FIND("/";A1;1)+1;FIND("/";A1;FIND("/";A1;1)+1)-FIND("/";A1;1)-1);LEFT(A1;FIND("/";A1;1)-1))+TIMEVALUE(RIGHT(A1;8))
现在是有趣的部分，即将所有这些公式连接到一个怪物中以获得日期
4a) 首先输入 =DATE(1;2;3)
4b）用（3c）替换1，用（3b）替换2，用（3a）替换3......不要复制前导“=”
4c)
=DATE(MID(A1;FIND("/";A1;FIND("/";A1;1)+1)+1;4);MID(A1;FIND("/";A1;1)+1;FIND("/";A1;FIND("/";A1;1)+1)-FIND("/";A1;1)-1);LEFT(A1;FIND("/";A1;1)-1))+TIMEVALUE(RIGHT(A1;8))
You don't see the time, only the date! .... Remember to give a custom cell format displaying date AND time, i.e. "DD.MM.YYYY hh:mm:ss"
你看不到时间，只有日期！....记得给一个自定义的单元格格式显示日期和时间，即“DD.MM.YYYY hh:mm:ss”
OK ... this formula is absolutely unreadable and un-understandable, so you might want to display intermediate results in (temporary) fields/columns.
好的...这个公式绝对不可读且难以理解，因此您可能希望在（临时）字段/列中显示中间结果。
And this formula will work only if the input string is more or less strictly formated. It can cope with some added blanks at the first and second numbers, but starting with YYYY it becomes tricky. Then other concepts need to be included, like removing all blanks by =SUBSTITUTE(A1;" ";"")before all others, etc. etc.
并且这个公式只有在输入字符串或多或少严格格式化时才有效。它可以处理第一个和第二个数字处添加的一些空白，但从 YYYY 开始就变得棘手了。然后需要包含其他概念，例如=SUBSTITUTE(A1;" ";"")在所有其他概念之前删除所有空格等。
Hint: I am always building complex formulae like this: isolate the terms in individual cells and later merge them to one large formula
提示：我总是像这样构建复杂的公式：分离单个单元格中的术语，然后将它们合并为一个大公式
