如何使用 VBA 将 mm/dd/yyyy 更改为 dd/mm/yyyy
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18668817/
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
How to change mm/dd/yyyy to dd/mm/yyyy using VBA
提问by user2634936
I have a problem in converting mm/dd/yyyy to dd/mm/yyyy date format using VBA.
我在使用 VBA 将 mm/dd/yyyy 转换为 dd/mm/yyyy 日期格式时遇到问题。
I have a table like this:
我有一张这样的表：
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.
仅供参考，该表是从报告工具自动生成的。“字符串操作”或任何 excel 函数有帮助吗？希望知道如何解决这个问题的人能给我一些想法。
回答by AndASM
Well, from the way you've worded your question I'm not convinced you actually want or need VBA.
好吧，从你提出问题的方式来看，我不相信你真的想要或需要 VBA。
Looking at the image you posted it would appear the first cell contains the string 05/06/2013 - 05/10/2013not 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.
查看您发布的图像，它会显示第一个单元格包含字符串05/06/2013 - 05/10/2013而不是日期05/06/2013。因此，您需要做的第一件事就是拆分各个部分，以便内置的 Excel 或 VBA 函数可以对其进行转换。
By Excel Formulas
通过 Excel 公式
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.
因此，我们可以使用 SEARCH 或 FIND 来查找“-”并动态执行操作。但我觉得很懒，所以我假设字符串的前 10 个字符是第一个日期，最后 10 个字符是第二个日期。源字符串上的 TRIM 函数应该使这在前后有额外空格的情况下更安全一些。
So if our string 05/06/2013 - 05/10/2013is in cell A2, we can put =LEFT(TRIM(A2),10)in B2 and =RIGHT(TRIM(A2),10)in C2.
所以如果我们的字符串05/06/2013 - 05/10/2013在单元格 A2 中，我们可以放入=LEFT(TRIM(A2),10)B2 和=RIGHT(TRIM(A2),10)C2。
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.
现在这些仍然是字符串。通常我会使用 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。
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.
从这里我们可以使用 TEXT 函数（很像 VBA 中的格式函数）和一些字符串连接将它们重新组合成原始的单单元格日期范围格式。假设这是想要的结果。
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:
所以我们的最后一个单元格 F2 将是=TEXT(D2,"dd/MM/yyyy") & " - " & TEXT(E2,"dd/MM/yyyy"). 我们当然可以将所有这些公式组合成一个大混乱，如下所示：
=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")
By Visual Basic for Applications
通过 Visual Basic 应用程序
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.
这是相同的过程，只是使用 VBA 函数和语法而不是 Excel 公式。现在，无论出于何种原因，DateValue 的 VBA 版本都将接受我的 Excel 副本中的这些日期。所以我会用它。
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
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).
这可以通过运行提供的测试子来测试，或者 ChangeDateFormat 可以用作 excel 公式中的用户定义函数=ChangeDateFormat(A2)。
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...
请注意，在传递给 Format 的日期格式中，我对日期分隔符进行了转义，\/而不是直接/输入。这是因为 Format 函数将自动替换/为 Windows 设置中的日期分隔符。而且由于我使用现代计算机友好的日期格式，因此我的分隔符是破折号...
Footnote
脚注
* Life would be so much easier if people would just use ISO 8601. It exists for a reason, a good reason.
* 如果人们只使用ISO 8601，生活会容易得多。它存在是有原因的，一个很好的理由。
回答by SeiferTim
format(varDate, "dd/MM/yyyy")
format(varDate, "dd/MM/yyyy")
