将日期格式更改为 yyyy-mm-dd [英] Changing the date format to yyyy-mm-dd

问题描述

我有一个日期列，其中包含混合格式的日期.例如:

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

所以，基本上一列中有两种不同的格式:dd.mm.yyyy 和 mm/dd/yyyy.我正在尝试编写一个 VBA 脚本来将列中所有日期的格式更改为 yyyy-mm-dd.这就是我到目前为止所得到的:

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:

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

我知道这不是一段优雅的代码，但我是 VBA 初学者，所以请原谅我.该代码的问题在于，当我将 Format 函数中的参数从 yyyy-mm-dd 更改为 dd/mm/yyyy 时，它只是将未更改的 A 列重写为 B 列它有效，但仅适用于 mm/dd/yyyy 格式的日期，并且保持 dd.mm.yyyy 不变.如有任何建议，我将不胜感激.

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.

推荐答案

更新:新答案

这是一个可以完成工作的解决方案！子例程包括一个执行替换的函数(该函数本身非常有用！).运行 sub，A 列中的所有出现都将被修复.

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

工作原理:我有一个漂亮的 RegexReplace 函数，它允许您使用正则表达式进行替换.sub mearly 遍历您的 A 列，并对您提到的那两种情况进行正则表达式替换.我首先使用 Instr() 的原因是确定它是否需要替换，以及哪种类型.从技术上讲，您可以跳过此步骤，但是在不需要它的单元格上进行替换非常昂贵.最后，我将单元格格式化为您的自定义日期格式，无论内部是什么以确保安全.

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.

如果您不熟悉 Regex(参考:http://www.regular-expressions.info/)，我使用的表达方式是:

In case you aren't familiar with Regex (for ref: http://www.regular-expressions.info/), the expression I am using is:

• () 中的每一项都是捕获组 - 也就是你想弄乱的东西
• d 代表数字 [0-9].
• {2} 表示 2 of，{4} 表示 4 of.为了安全起见，我在这里已经明确了.
• . 之前的 在第一次替换中需要因为."有特殊意义.
• 在 VBA 正则表达式中，您可以使用 $ + no 来引用捕获组.组的.这就是我翻转 3 个项目的顺序的方式.

这篇关于将日期格式更改为 yyyy-mm-dd的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！