vba 格式化时间从 dd/mm/yyyy hh:mm:ss 到 yyyy.mm.dd hh:mm
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16145767/
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
Formatting time from dd/mm/yyyy hh:mm:ss to yyyy.mm.dd hh:mm
提问by atira
I received the following task: turn "dd/mm/yyyy hh:mm:ss" time to "yyyy.mm.dd hh:mm" format.
我收到了以下任务：将“dd/mm/yyyy hh:mm:ss”时间转换为“yyyy.mm.dd hh:mm”格式。
I was hoping that a simple Format(activecell.value, "yyyy.mm.dd hh:mm") would help, but. In some cases it works. However, in some special cases, it doesn't. When the day is between 01-12 (interchangeable with months!), then it messes up the days with months. (Probably because of US style date format.)
我希望一个简单的 Format(activecell.value, "yyyy.mm.dd hh:mm") 会有所帮助，但是。在某些情况下它有效。但是，在某些特殊情况下，它不会。当一天在 01-12 之间时（可以与月份互换！），那么它就会把日子和月份混为一谈。（可能是因为美国风格的日期格式。）
For example, original text string: 04/06/2013 09:00:00
例如，原文字符串：04/06/2013 09:00:00
Expected: 2013.06.04 09:00
预计：2013.06.04 09:00
Result: 2013.04.06 09:00 (day and month have been swapped)
结果：2013.04.06 09:00（日期和月份已互换）
I tried to overcome it by formatting the input, I gave it a "dd/mm/yyyy hh:mm:ss" custom format. Didn't help, it still swaps the day and the month.
我试图通过格式化输入来克服它，我给了它一个“dd/mm/yyyy hh:mm:ss”自定义格式。没有帮助，它仍然交换日期和月份。
So I have no ideas anymore, but regular expressions. Date format is always the same so just moving the parts of the string would be okay. However, I don't know how that works and google searching brings up confusing results.
所以我没有想法了，只有正则表达式。日期格式始终相同，因此只需移动字符串的各个部分就可以了。但是，我不知道它是如何工作的，谷歌搜索带来了令人困惑的结果。
Can anyone help me? Of course if there is a more elegant way to do it, that's also welcome.
谁能帮我？当然，如果有更优雅的方式来做到这一点，那也是受欢迎的。
回答by user2140261
Try using this Custom Format:
尝试使用此自定义格式：
yyyy/mm/dd h:mm;@
To enter it, right click on the cell and Choose Format Cell:
要输入它，请右键单击单元格并选择格式单元格：
Then under the NumberTab Select Customfrom the listbox. And enter the provided format in the Type:textbox.
然后在Number选项卡下Custom从列表框中选择。并在Type:文本框中输入提供的格式。
If that doesn't work. Are you *****Absolutely***** Positive that 04/06/2013 09:00:00 IS April 6, 2013 and NOT June 4, 2013??
如果那不起作用。你*****绝对***** 04/06/2013 09:00:00 是 2013 年 4 月 6 日而不是 2013 年 6 月 4 日吗？
If this still doesn't work and you have verified that dates are correct.
如果这仍然不起作用并且您已确认日期正确。
Sub ChangeDateFormat()
Application.ScreenUpdating = False
Dim CurrentCell As Range
Dim LastRow As Long
Dim RegEx As Object
Set RegEx = CreateObject("vbscript.regexp")
RegEx.Global = True
LastRow = Range("A" & Rows.Count).End(xlUp).Row 'Get The Last Row in Column Change A as Needed
For Each CurrentCell In Range("A1:A" & LastRow) ' Loop through all cells. Change Column as needed
If InStr(CurrentCell.Value, "/") <> 0 Then 'To make sure only convert non converted ones
RegEx.Pattern = "(\d{2})/(\d{2})/(\d{4}) (\d{2}):(\d{2}):(\d{2})" ' Seperate all parts of imported Data into groups
CurrentCell.Value = RegEx.Replace(CurrentCell.Value, ".. :") ' Change order of groups and place into cell.
End If
Next
Application.ScreenUpdating = True
End Sub
*****NOTE: ***** This will only work if ALLdate values are of dd/mm/yyyy hh:mm:ssif they are not you will have to add some Error Handling and possibly slightly modify the code as it WILLcause problems.
*****注：*****这只会工作，如果所有的日期值是的dd/mm/yyyy hh:mm:ss，如果他们都没有，你将不得不添加一些错误处理，并可能稍微修改代码，因为它WILL引起问题。
This will also work on dates that are inside of other text. If the value of A1is
这也适用于其他文本中的日期。如果该值A1就是
This is a test 04/06/2013 09:00:00 Lets see what happens
This is a test 04/06/2013 09:00:00 Lets see what happens
Then the new value will be
那么新值将是
This is a test 2013.06.04 09:00 Lets see what happens
This is a test 2013.06.04 09:00 Lets see what happens
回答by Musaab Zeeshan
its simple. . . just follow the steps select the entire column or even required cells press right click and select format cell option, a tab window will open from number tab select date , from locale drop down menu select English (South Africa) from given options in type select your desired reverse format . . . and you done :)
这很简单。. . 只需按照步骤选择整列或什至所需的单元格按右键单击并选择格式单元格选项，将打开一个选项卡窗口，从数字选项卡选择日期，从区域设置下拉菜单中选择英语（南非）从类型中的给定选项中选择您的所需的反向格式。. . 你完成了:)
回答by Atull
If the month and day are in the wrong place, you can use "DATE(YEAR(A1),DAY(A1),MONTH(A1))", provided it's in date format, and "DATE(MID(A1,7,4),LEFT(A1,2),MID(A1,4,2))"if it is in text format
如果月和日在错误的地方，您可以使用"DATE(YEAR(A1),DAY(A1),MONTH(A1))"，前提是它是日期格式，"DATE(MID(A1,7,4),LEFT(A1,2),MID(A1,4,2))"如果是文本格式
