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

问题描述

我目前在美国EST，我公司使用的日期格式是

月/日/年份小时：分钟：秒

我需要复制并粘贴从其他地区提取的日期/时间，并且它们具有混合格式。有些与我所使用的格式相同，而其他则使用英国格式

日/月/年小时：最小：秒

目前，我必须手动编辑英国格式以与其余的匹配，以便我的日期公式不会有错误。这开始是一个问题，因为我面对200多条记录，每条记录都有不同的时间。

有没有一个代码或公式可以用来解决这个问题问题 ？我知道datetime格式可以通过系统区域设置来解决，但是这并不是有帮助的，因为改变为任何格式仍然需要我手动编辑那些不受影响的文件。

我正在考虑使用Visual Basic进行遍历，使用每行的唯一ID来识别它是英国还是美国。我在正确的轨道上吗？

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

提前感谢

解决方案

您应该始终将转换为日期的东西转换为日期。日期将始终保持日期，您可以以任何所需的格式显示日期。它甚至可以从一种语言转换到另一种语言。

所以如果你的来源是一个日期你没事。如果它是一个字符串，我会这样做：

1. 使用= DATEVALUE（）转换源字符串...这将工作，如果月份名称是系统语言


2. 如果临时更改系统语言不是一个选项，接下来将是= DATE（yy，mm，dd），因此必须使用= LEFT（...），= MID（...），= RIGHT（...）



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

   
   
   

   A1包含 JANFEBMAR ...

   
   
   

   A2包含JÄNFEBMÄR...

   
   
   

   转换公式 = MID（A2; FIND （MAR; A1）; 3）

编辑 回复Kyle的评论：

除了德国（奥地利）地区将立即将此字符串识别为日期的事实，让我们将问题分解成可咀嚼片。你的日期字符串在A1中被淡化

1. 时间部分很容易：它是一个加法术语 TIMEVALUE（RIGHT A1; 8））



2. 分割我们必须在动态位置对抗分隔符的日期。分隔符的位置可以通过

   
   
   

   2a） = FIND（/; A1; 1） ...找到第一个分隔符的位置

   
   
   

   2b） = FIND（/; A1; 4）。第二个分隔符，因为第一个数字可以只有1或2个字符，之前没有空格 - 或者我们必须用术语（2a）+1代替常数4+1

   
   
   

   2c） = FIND（/; A1; FIND（/; A1; 1）+1） ...更安全的版本（2b） p>




3. 现在我们有大部分事情准备好构建我们左边的中间和右边的

   
   
   

   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） ... 2nd figure ... start： （2a）+1，num_Chars =（2c） - （2a）-1

   
   
   

   3c） = MID（A1; FIND（/ ; A1; FIND（/; A1; 1）+1）+1; 4） ... 第3位数字 ...开始：=（2c）+ 1，num_chars = 4 - 一年是alwa ys 4位数。更抽象的情况将是（3b），（2c）是第一个空白


的 = FIND（） / ol>



如何解读特别是第1和第2位数字（即MM / DD或DD / MM）取决于您。




1. 现在有趣的部分，即将所有这些公式连接成一个怪物来获取日期

   
   
   

   4a）从头开始输入= DATE（1; 2; 3）

   
   
   

   4b）将1乘以（3c），2乘以（3b），3乘（3a）...不要复制领先的=

   
   
   

   4c） = DATE（MID（A1; FIND（/; A1; FIND（/; A1; 1）+1）+1; 4）; MID（A1; FIND（ /; A1; 1）+1; FIND（ /; A1; FIND（ /; A1; 1）+1 ） - 查找（ /; A1; 1）-1）; LEFT（A1; FIND（ /; A1; 1）-1））+ TIMEVALUE（RIGHT（A1; 8））




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





OK ...这个公式是绝对不可读和不可理解的，所以您可能希望在（临时）字段/列中显示中间结果。





只有输入字符串为或多或少严格形成。它可以应付一些添加的空白在第一和第二个数字，但从YYYY开始变得棘手。那么需要包括其他的概念，比如在所有其他人等之前，通过 = SUBSTITUTE（A1;;）删除所有的空格。





提示：我总是构建这样的复杂公式：隔离单个单元格中的术语，然后将它们合并成一个大型公式。




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

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.

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 ?

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

Thanks in advance

解决方案

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

   A2 contains JÄNFEBMÄR...

   conversion formula =MID(A2;FIND("MAR";A1);3)

Edit in 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

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) ... find position of first delimiter

   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) ... safer version of (2b)

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

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

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

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.

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屋！