将日期格式更改为yyyy

本文介绍了将日期格式更改为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： 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: 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.