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

问题描述

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

A

21.03.1990

03/21/1990

因此，一列中基本上有两种不同的格式： 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 
 
使用ActiveWorkbook.Worksheets（Sheet1）
设置rLastCell = .Range（A65536）。End（xlUp）
 On Error Resume Next 
对于每个单元格.Range（A1：A& rLastCell.Row）
 LValue = Format（cell.Value，yyyy-mm-dd）
 .Range（B& i）.Value = LValue 
i = i + 1 
下一个单元格
错误GoTo 0 
结束
 
结束Sub 
  

我知道这不是典雅的代码，但我是VBA初学者，请原谅我。
该代码的问题是，只要将格式函数中的参数从 yyyy-mm-dd 更改为 dd / mm / yyyy 它有效，但只适用于格式为 mm / dd / yyyy 的日期，并留下 dd.mm.yyyy untouched。

解决方案

UPDATED：NEW ANSWER

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

  Sub FixDates（）
 
 Dim单元格作为范围
 Dim lastRow As Long 
 
 lastRow = range（A& Rows.count）.End（xlUp）.Row 
 
对于每个单元格在范围内（A1：A& lastRow）
如果InStr（cell.Value，。）< 0然后
 cell.Value = RegexReplace（cell.Value，_ 
（\d {2}）\。（\d {2}）\。（\d {4 }），$ 3- $ 2- $ 1）
 End If 
如果InStr（cell.Value，/）<> 0然后
 cell.Value = RegexReplace（cell.Value，_ 
（\d {2}）/（\d {2}）/（\d {4}） $ 3- $ 1- $ 2）
 End If 
 cell.NumberFormat =yyyy-mm-d; @
 Next 
 
 End Sub 
将此功能放在同一个模块中： 

 函数RegexReplace（ByVal text As String，_ 
 ByVal replace_what As String，_ 
 ByVal replace_with As String）As String 
 
 Dim RE As Object 
设置RE = CreateObject（vbscript.regexp）
 
 RE.pattern = replace_what 
 RE.Global = True 
 RegexReplace = RE.Replace（text，replace_with） 
 
结束功能
  

工作原理：我有一个漂亮的RegexReplace函数，可以使用正则表达式来替换。这个子句通过你的A列循环，并对你提到的那些2个例子进行正则表达式替换。我首先使用Instr（）的原因是确定是否需要更换，以及哪种。你可以在技术上跳过这个，但是对不需要它的单元格进行替换是非常昂贵的。最后，我将单元格格式化为您的自定义日期格式，而不管内部的安全措施如何。

如果您不熟悉正则表达式（for ref： http://www.regular-expressions.info/ ），我使用的表达式是：

• （）中的每个项目都是捕获组，也就是您想要混淆的东西，


• \ d代表一个数字[0-9]。


• {2}表示2，{4}表示4。我已经在这里明确安全了。


• \之前的。在第一次替换是需要。具有特殊意义。


• 在VBA正则表达式中，使用$ + no引用捕获组。的组。这是我如何翻转3个项目的顺序。

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

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

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.

解决方案

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

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

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

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