vba 将日期格式更改为 yyyy-mm-dd
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7106238/
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
Changing the date format to yyyy-mm-dd
提问by Jandrejc
I've got a date column that contains dates in mixed format. For example:
我有一个日期列，其中包含混合格式的日期。例如：
A
21.03.1990
03/21/1990
A
21.03.1990
03/21/1990
So, basically there are two different formats in one column: dd.mm.yyyyand mm/dd/yyyy. I'm trying to write a VBA script to change format of all dates in the column to be yyyy-mm-dd. That's what I've got so far:
因此，基本上一列中有两种不同的格式：dd.mm.yyyy和mm/dd/yyyy. 我正在尝试编写一个 VBA 脚本来将列中所有日期的格式更改为yyyy-mm-dd. 这就是我到目前为止所得到的：
Sub changeFormat()
Dim rLastCell As Range
Dim cell As Range, i As Long
Dim LValue As String
i = 1
With ActiveWorkbook.Worksheets("Sheet1")
Set rLastCell = .Range("A65536").End(xlUp)
On Error Resume Next
For Each cell In .Range("A1:A" & rLastCell.Row)
LValue = Format(cell.Value, "yyyy-mm-dd")
.Range("B" & i).Value = LValue
i = i + 1
Next cell
On Error GoTo 0
End With
End Sub
I know that it's not elegant piece of code, but I'm beginner with VBA so please forgive me.
The problem with that code is that it just rewrite unchanged A column into column B, when I change argument in Format function from yyyy-mm-ddto dd/mm/yyyyit works, but only for dates in format mm/dd/yyyy, and leaves dd.mm.yyyyuntouched. I would appreciate any advice.
我知道这不是一段优雅的代码，但我是 VBA 的初学者，所以请原谅我。该代码的问题在于，它只是将未更改的 A 列重写为 B 列，当我将 Format 函数中的参数从 更改为 时yyyy-mm-dd，dd/mm/yyyy它可以工作，但仅适用于 format 中的日期mm/dd/yyyy，并且保持dd.mm.yyyy不变。我将不胜感激任何建议。
回答by aevanko
UPDATED: NEW ANSWER
更新：新答案
Here is a solution that will do the job! The sub routine includes a function that does the replacement (the function itself is really useful!). Run the sub and all occurances in column A will be fixed.
这是一个可以完成工作的解决方案！子例程包括一个执行替换的函数（该函数本身非常有用！）。运行 sub 并且 A 列中的所有出现都将被修复。
Sub FixDates()
Dim cell As range
Dim lastRow As Long
lastRow = range("A" & Rows.count).End(xlUp).Row
For Each cell In range("A1:A" & lastRow)
If InStr(cell.Value, ".") <> 0 Then
cell.Value = RegexReplace(cell.Value, _
"(\d{2})\.(\d{2})\.(\d{4})", "--")
End If
If InStr(cell.Value, "/") <> 0 Then
cell.Value = RegexReplace(cell.Value, _
"(\d{2})/(\d{2})/(\d{4})", "--")
End If
cell.NumberFormat = "yyyy-mm-d;@"
Next
End Sub
Place this function in the same module:
将此函数放在同一模块中：
Function RegexReplace(ByVal text As String, _
ByVal replace_what As String, _
ByVal replace_with As String) As String
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
RE.pattern = replace_what
RE.Global = True
RegexReplace = RE.Replace(text, replace_with)
End Function
How it works: I have a nifty RegexReplace function that allows you to do replace using regular expressions. The sub mearly loops through your A column and does a regex replace for those 2 cases you mentioned. The reason I use an Instr() first is to determain if it needs the replacement, and which kind. You could technically skip this but doing replace on cells that don't need it is really costly. At the end I format the cell to your custom date format regardless of what's inside for safe measure.
它是如何工作的：我有一个漂亮的 RegexReplace 函数，它允许您使用正则表达式进行替换。sub mearly 遍历您的 A 列，并对您提到的那两种情况进行正则表达式替换。我首先使用 Instr() 的原因是确定它是否需要替换，以及哪种类型。从技术上讲，您可以跳过此步骤，但是在不需要它的单元格上进行替换非常昂贵。最后，我将单元格格式化为您的自定义日期格式，无论内部是什么以确保安全。
In case you aren't familiar with Regex (for ref: http://www.regular-expressions.info/), the expression I am using is:
如果您不熟悉 Regex（参考：http: //www.regular-expressions.info/），我使用的表达式是：
- Each item in () are capture groups - aka, the stuff you want to mess with
- \d stands for a number [0-9].
- {2} means 2 of, and {4} mean 4 of. I have been explicit here for safety.
- The \ before the . in the first replace is needed since "." has special meaning.
- In VBA regex, you refer to capture groups by using $ + no. of group. This is how I flip the order of the 3 items.
- () 中的每个项目都是捕获组 - 也就是你想弄乱的东西
- \d 代表数字 [0-9]。
- {2} 表示 2 个，{4} 表示 4 个。为了安全起见，我在这里已经明确了。
- 之前的\。在第一次替换中需要因为“。” 有特别的意义。
- 在 VBA 正则表达式中，您可以使用 $ + no 来引用捕获组。组的。这就是我翻转 3 个项目的顺序的方式。
回答by Jean-Fran?ois Corbett
You don't really need VBA for this. This one-liner worksheet formula will do the trick:
为此，您实际上并不需要 VBA。这个单行工作表公式可以解决问题：
=IF(ISERROR(FIND(".",A1)),IF(ISERROR(FIND("/",A1)),"invalid format",
DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2))),
DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2)))
This assumes the day and month are always given as two-digit numbers (e.g. always 03 and never just 3) and the year has four digits (i.e. "restricted" to years 1000-9999). But if this is not the case for you, then the formula can easily be adjusted to suit your purpose.
这假设日期和月份总是以两位数的形式给出（例如总是 03 而不是 3）并且年份有四位数字（即“限制”为 1000-9999 年）。但如果您不是这种情况，则可以轻松调整公式以适合您的目的。
回答by transistor1
See if this does what you want. You may have to tailor it a bit for your own application.
看看这是否符合您的要求。您可能需要根据自己的应用程序对其进行一些调整。
Hope this helps!
希望这可以帮助！
Sub convertDates()
Dim rRng As Range
Dim rCell As Range
Dim sDest As String
Dim sYear, sMonth, sDay, aDate
'Range where the dates are stored, excluding header
Set rRng = Sheet1.Range("A2:A11")
'Column name of destination
sDest = "B"
'You could also use the following, and just select the range.
'Set rRng = Application.selection
For Each rCell In rRng.Cells
sYear = 99999
If InStr(rCell.Value, ".") > 0 Then
aDate = Split(rCell.Value, ".")
If UBound(aDate) = 2 Then
sDay = aDate(0)
sMonth = aDate(1)
sYear = aDate(2)
End If
ElseIf InStr(rCell.Value, "/") > 0 Then
aDate = Split(rCell.Value, "/")
If UBound(aDate) = 2 Then
sDay = aDate(1)
sMonth = aDate(0)
sYear = aDate(2)
End If
End If
With rCell.Range(sDest & "1")
If sYear <> 99999 Then
On Error Resume Next
.Value = "'" & Format(CDate(sMonth & "/" & sDay & "/" & sYear), "YYYY-MM-DD")
'If it can't convert the date, just put the original value in the dest
'cell. You can tailor this to your own preference.
If Err.Number <> 0 Then .Value = rCell.Value
On Error GoTo 0
Else
.Value = rCell.Value
End If
End With
Next
End Sub
回答by pnuts
@Jean-Fran?ois Corbett has a formula solution that works but that might be shortened by more than half by foregoing the error message (on the basis that #VALUE!is as informative) and another IF, both DATEs, applying IFERROR rather than ISERROR, and SUBSTITUTE in place of one LEFT, MID, RIGHT set:
@Jean-Fran?ois Corbett 有一个有效的公式解决方案，但通过前面的错误消息（基于#VALUE!提供信息）和另一个 IF，两个 DATE，应用 IFERROR 而不是 ISERROR，可能会缩短一半以上，并且SUBSTITUTE 代替一组左、中、右：
=IFERROR(1*(MID(A1,4,3)&LEFT(A1,3)&RIGHT(A1,4)),1*SUBSTITUTE(A1,".","/"))
This applies the interpretation mentioned by @Issun in the comment to the OP and assumes the output will be to cells formatted yyyy-mm-dd.
这将@Issun 在评论中提到的解释应用于 OP，并假设输出将是格式化的单元格yyyy-mm-dd。
May be written with a subroutine like so:
可以用这样的子程序编写：
Sub Macro1()
Range("B1:B10").Formula = "=IFERROR(1*(MID(A1,4,3)&LEFT(A1,3)&RIGHT(A1,4)),1*SUBSTITUTE(A1,""."",""/""))"
End Sub
回答by ArsedianIvan
Here is a simple solution to this:
这是一个简单的解决方案：
Sub ChangeFormat1()
Dim ws as Worksheet
Set ws = Thisworkbook.Sheets("Sheet1")
LR = ws.cells(rows.count,1).End(Xlup).Row
with ws
For I = 1 to LR
.cells(I,2).value = .cells(I,1).value
Next
End with
ws.range("B1:B" & LR).numberformat = "yyyy-mm-dd"
End Sub
回答by George Wang
Month(Date) & "-" & Day(Date) & "-" & Year(Date)
月(日期) & "-" & 日(日期) & "-" & 年(日期)
