如何使用VBA将mm/dd/yyyy更改为dd/mm/yyyy [英] How to change mm/dd/yyyy to dd/mm/yyyy using VBA

问题描述

使用VBA将mm/dd/yyyy日期格式转换为dd/mm/yyyy日期格式时遇到问题.

I have a problem in converting mm/dd/yyyy to dd/mm/yyyy date format using VBA.

我有一个这样的表:

仅供参考，该表格是通过报告工具自动生成的.字符串操作"或任何excel函数都可以帮助吗?希望任何知道如何解决此问题的人都能给我一些想法.

fyi, the table is auto generated from a reporting tool. Can "string manipulation" or any excel function help? Hope anyone who know how to solve this problem can give me some idea.

推荐答案

从您对问题的措辞来看，我并不认为您确实需要或需要VBA.

Well, from the way you've worded your question I'm not convinced you actually want or need VBA.

查看您发布的图片，它将出现在第一个单元格中，该字符串包含字符串 05/06/2013-05/10/2013 而不是日期 05/06/2013 >.因此，您需要做的第一件事就是拆分零件，以便内置的Excel或VBA函数可以对其进行转换.

Looking at the image you posted it would appear the first cell contains the string 05/06/2013 - 05/10/2013 not the date 05/06/2013. So the first thing you need to do is split out the parts so the built in Excel or VBA functions can convert it.

因此，我们可以使用SEARCH或FIND查找-"并动态地执行操作.但是我感到很懒，所以我只假设字符串的前10个字符是第一个日期，而后10个字符是第二个日期.源字符串上的TRIM函数应使其更安全一些，以防在此之前或之后有多余的空格.

So we could use SEARCH or FIND to find the "-" and do things dynamically. But I'm feeling lazy so I'll just assume the first 10 characters of the string are the first date and the last 10 characters are the second date. A TRIM function on the source string should make this a bit safer in case of extra spaces before or after.

因此，如果我们的字符串 05/06/2013-05/10/2013 在A2单元格中，我们可以将 = LEFT(TRIM(A2)，10)在B2中为 = RIGHT(TRIM(A2)，10)在C2中.

So if our string 05/06/2013 - 05/10/2013 is in cell A2, we can put =LEFT(TRIM(A2),10) in B2 and =RIGHT(TRIM(A2),10) in C2.

现在这些仍然是字符串.通常，我会使用DATEVALUE将字符串转换为日期，但是我的Excel副本不喜欢那些疯狂的废话*日期格式.因此，我们将日期解析为DATE函数.放置 = DATE(RIGHT(B2,4)，LEFT(B2,2)，MID(B2,4,2))和 = DATE(RIGHT(C2,4)，LEFT(C2,2)，MID(C2,4,2))分别进入单元格C2和D2.

Now these are still strings. Normally I'd use DATEVALUE to convert the strings to dates, but my copy of Excel doesn't like those crazy nonsense* date formats. So we will parse the dates into the DATE function. Putting =DATE(RIGHT(B2,4),LEFT(B2,2),MID(B2,4,2)) and =DATE(RIGHT(C2,4),LEFT(C2,2),MID(C2,4,2)) into cells C2 and D2 respectively.

在这里，我们可以使用TEXT函数(非常类似于VBA中的format函数)和一些字符串合并为原始的单单元格日期范围格式来重新组合它们.假设这是理想的结果.

From here we can recombine them using the TEXT function (much like the format function in VBA) and some string concatenation into your original single-cell date range format. Assuming that's the desired result.

因此，我们的最后一个单元格F2将是 = TEXT(D2，"dd/MM/yyyy")&-"&TEXT(E2，"dd/MM/yyyy").我们当然可以将所有这些公式组合成一个大混乱，就像这样:

So our final cell, F2, would be =TEXT(D2,"dd/MM/yyyy") & " - " & TEXT(E2,"dd/MM/yyyy"). We could of course combine all those formulas into one big mess like so:

=TEXT(DATE(RIGHT(LEFT(A2,10),4),LEFT(LEFT(A2,10),2),MID(LEFT(A2,10),4,2)),"dd/MM/yyyy") & " - " & TEXT(DATE(RIGHT(RIGHT(TRIM(A2),10),4),LEFT(RIGHT(TRIM(A2),10),2),MID(RIGHT(TRIM(A2),10),4,2)),"dd/MM/yyyy")

通过Visual Basic for Applications

这里是相同的过程，只是使用VBA函数和语法而不是Excel公式.现在，无论出于何种原因，VBA版本的DateValue都将在我的Excel副本中接受这些日期.所以我会用它.

By Visual Basic for Applications

It's the same process here, just using VBA functions and syntax instead of Excel formulas. Now for whatever reason the VBA version of DateValue will accept those dates in my copy of Excel. So I'll use it.

Public Function ChangeDateFormat(inputString As String) As String
  Dim firstDate As Date
  Dim secondDate As Date
  Dim trimmedInput As String
  trimmedInput = Trim$(inputString)
  firstDate = DateValue(Left$(trimmedInput, 10))
  secondDate = DateValue(Right$(trimmedInput, 10))

  ChangeDateFormat = Format(firstDate, "dd\/MM\/yyyy") & " - " & Format(secondDate, "dd\/MM\/yyyy")
End Function

Public Sub Test()
  Sheet1.[B2] = ChangeDateFormat(Sheet1.[A2])
End Sub

这可以通过运行提供的Test子项进行测试，或者ChangeDateFormat可以用作excel公式 = ChangeDateFormat(A2)中的用户定义函数.

This can be tested either by running the provided Test sub, or ChangeDateFormat can be used as a user defined function in an excel formula =ChangeDateFormat(A2).

请注意，在传递给Format的日期格式中，我转义了日期分隔符 \/，而不仅仅是将/放入其中.这是因为Format函数将自动替换/，带有Windows设置中的日期分隔符.由于我使用的是现代计算机友好的日期格式，因此我的分隔符是破折号...

Note, in the date formats passed to Format, I escaped the date separator \/ instead of just putting / in. This is because the Format function will automatically replace / with the date separator from your Windows settings. And since I use a modern computer friendly date format, my seperators are dashes...

*如果人们只使用 ISO 8601 ，生活将会变得更加轻松.它的存在是有原因的，有充分的理由.

* Life would be so much easier if people would just use ISO 8601. It exists for a reason, a good reason.

这篇关于如何使用VBA将mm/dd/yyyy更改为dd/mm/yyyy的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！