将日期格式更改为yyyy-mm-dd

I've got a date column that contains dates in mixed format. For example:

我有一个日期列，包含了混合格式的日期。例如:

A
21.03.1990
03/21/1990

一个21.03.1990 03/21/1990

So, basically there are two different formats in one column: dd.mm.yyyy and 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:

因此，基本上在一列中有两种不同的格式:ddl .mm。yyyy和mm / dd / yyyy。我正在编写一个VBA脚本，将列中所有日期的格式更改为yyyyyy -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-dd to dd/mm/yyyy it works, but only for dates in format mm/dd/yyyy, and leaves dd.mm.yyyy untouched. I would appreciate any advice.

我知道这不是一段优雅的代码，但是我是VBA的初学者，所以请原谅我。这段代码的问题是，当我将格式函数的参数从yyyyy -mm-dd更改为dd/mm/ yyyyy时，它只对格式为mm/dd/yyyy的日期有效，并将。mm保留。yyyy不变。如有任何建议，我将不胜感激。

6 个解决方案

#1

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})", "$3-$2-$1")
    End If
    If InStr(cell.Value, "/") <> 0 Then
        cell.Value = RegexReplace(cell.Value, _
        "(\d{2})/(\d{2})/(\d{4})", "$3-$1-$2")
    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函数，它允许您使用正则表达式进行替换。子mearly循环遍历您的A列，并对您提到的这两种情况执行regex替换。我首先使用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].
• \d代表数字[0-9]。
• {2} means 2 of, and {4} mean 4 of. I have been explicit here for safety.
• {2}表示2，{4}表示4。为了安全起见，我已经说得很清楚了。
• 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.
• 在VBA regex中，您可以使用$ + no来引用捕获组。的组。这就是我如何翻转这3项的顺序。

#2

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

#3

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)，一年有4位数(例如)。“限制”1000年- 9999年)。但如果不是这样，那么公式可以很容易地调整以适应您的目的。

#4

@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-Francois Corbett有一个有效的公式解决方案，但是通过放弃错误消息(基于#VALUE!和另一个IF，两个日期，应用IFERROR而不是ISERROR，并替换一个左、中、右集合:

=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注释中提到的解释，并假定输出将是格式化为yyyyy -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  

#5

Month(Date) & "-" & Day(Date) & "-" & Year(Date)

月(日)、“-”、日(日)、“-”、年(日)

#6

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