Excel 使用具有多种不同区域格式的日期/时间.VB?公式? [英] Excel using date/time with multiple different region formats. VB? Formulas?

问题描述

我目前在美国东部标准时间，我公司使用的日期格式是

I'm currently in US EST and the date format used within my company is

月/日/年 小时:分钟:秒

Month/Day/Year Hour:Minutes:sec

我需要复制和粘贴从其他地区提取的日期/时间，并且它们具有混合格式.有的和我的格式一样，有的用的是UK格式

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

日/月/年 时:分:秒

Day/Month/Year Hour:Min:sec

目前，我必须手动编辑英国格式以与其他格式匹配，以便我的日期公式不会出错.这开始成为一个问题，因为我面临着 200 多条记录，而且每条记录都有不同的时间.

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.

是否有代码或公式可以解决这个问题?我知道日期时间格式可以通过系统区域设置解决，但这无济于事，因为更改为任一格式仍然需要我手动编辑那些不受影响的..

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..

我正在考虑使用 Visual Basic 进行迭代，使用每一行的唯一 ID 来识别它是英国还是美国.我在正确的轨道上吗?

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 ?

否则有可用的excel公式或方法吗?

Otherwise is there an excel formula or method available for this?

提前致谢

推荐答案

您应该始终致力于将看起来像日期的内容转换为日期.日期将始终保持为日期，您可以以任何您想要的格式显示它.它甚至可以从一种语言过渡到另一种语言.

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:

1. 使用 =DATEVALUE() 转换源字符串...如果月份名称使用系统语言，这将起作用
2. 如果暂时无法更改系统语言，则下一个将是 =DATE(yy,mm,dd) 必须使用 =LEFT(...), =MID(...) 创建参数，=RIGHT(...)

3. 最终您必须将月份名称从一种语言转换为另一种语言，如下例所示:

1. convert the source string using =DATEVALUE() ... this will work if the month name is in the system language
2. 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(...)

3. eventually you must convert month names from one language to another like in the following example:

A1 包含 JANFEBMAR...

A2 包含 JÄNFEBMÄR...

转换公式=MID(A2;FIND("MAR";A1);3)

编辑回复凯尔的评论:

Edit in reply to Kyle's comment:

除了德国(奥地利)语言环境会立即将此字符串识别为日期这一事实之外，让我们将问题分解为可咀嚼的部分.您的日期字符串假定在 A1

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

1. 时间部分很简单:它是一个附加项TIMEVALUE(RIGHT(A1;8))

2. 分割日期，我们必须在动态位置与分隔符作斗争.分隔符的位置可以通过

1. the time part is easy: it is an additive term TIMEVALUE(RIGHT(A1;8))

2. 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) ... 找到第一个分隔符的位置

2a) =FIND("/";A1;1) ... find position of first delimiter

2b) =FIND("/";A1;4) ... 第二个分隔符，假设第一个数字只能是 1 或 2 个字符并且之前没有空格 - 或者我们必须替换由项 (2a)+1 组成的常数4"

2b) =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)+1

2c) =FIND("/";A1;FIND("/";A1;1)+1) ... (2b) 的更安全版本

2c) =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) ... 第一个图 ... (2a) 的长度-1

3a) =LEFT(A1;FIND("/";A1;1)-1) ... 1st figure ... length of (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)-1

3b) =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)-1

3c) =MID(A1;FIND("/";A1;FIND("/";A1;1)+1)+1;4) ... 第三数字 ... start:=(2c)+1, num_chars = 4 - 假设年份总是 4 位数字.更抽象的情况是 (3b) 和 (2c) 是第一个空白的 =FIND()

3c) =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

如何解释尤其是第一和第二个数字(即 MM/DD 或 DD/MM)取决于您.

How to interpret especially the 1st and 2nd figure (i.e. MM/DD or DD/MM) is up to you.

1. 现在是有趣的部分，即将所有这些公式连接到一个怪物中以获得日期

1. now comes the fun part, i.e. concatenating all these formulae into one monster to get the date

4a) 首先输入 =DATE(1;2;3)

4a) start by entering =DATE(1;2;3)

4b) 将 1 替换为 (3c)，将 2 替换为 (3b)，将 3 替换为 (3a) ... 不要复制前导="

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))

你看不到时间，只有日期！....记得给一个自定义的单元格格式显示日期和时间，即DD.MM.YYYY hh:mm:ss"

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"

好的……这个公式绝对不可读且难以理解，因此您可能希望在(临时)字段/列中显示中间结果.

OK ... this formula is absolutely unreadable and un-understandable, so you might want to display intermediate results in (temporary) fields/columns.

而且这个公式只有在输入字符串或多或少严格格式化时才有效.它可以处理在第一个和第二个数字处添加的一些空白，但从 YYYY 开始它变得棘手.然后需要包含其他概念，例如在所有其他概念之前通过 =SUBSTITUTE(A1;" ";"") 删除所有空格等.

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.

提示:我总是像这样构建复杂的公式:分离单个单元格中的术语，然后将它们合并为一个大公式

Hint: I am always building complex formulae like this: isolate the terms in individual cells and later merge them to one large formula

这篇关于Excel 使用具有多种不同区域格式的日期/时间.VB?公式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！