Excel日期和时间时间转换 [英] Excel Date& Time Conversion

问题描述

我希望你会好起来的，能否请你提及我该如何转换

I hope you will be fine, Can you please mention how do I convert this

2014年10月28日16:00:00 进入

10/28/2014 4:00 PM.

我想转换整个列.此外，如果您能提及VBA代码，将会有很多帮助.

I want to convert the whole column. Moreover if you can mention the VBA code that would be alot of help.

上述excel文件是通过从客户导入csv文件而创建的.我的国家是沙特阿拉伯，它具有不同的DateTime格式 d/m/y ，我还需要将已处理的Excel文件导入Access数据库.

The mentioned excel file was created by importing csv file from the customer. My country is Saudi Arabia which has different DateTime format d/m/y and I need also to import the processed excel file to Access database.

谢谢

推荐答案

以下是VBA中的代码段，该代码段将阿塞拜疆日期格式 mdy 转换为ar-SA日期格式 d/m/y .使用参数"A"运行 FixDates 会将文本 mdyy hh:mm:ss 转换为日期值 d/m/yyyy hh:mm:ss用于A列中的所有单元格.它使用的是VBScript类中的RegExp.

Here is a code snippet in VBA that converts Azerbaijan date format m.d.y to ar-SA date format d/m/y. Running the FixDates with parameter "A" should convert the text m.d.yy hh:mm:ss to the date value d/m/yyyy hh:mm:ss for all the cells in column A. It is using RegExp from VBScript class.

Option Explicit

Sub ConvertDates()
   Call FixDates("A")
End Sub

Sub FixDates(column As String)
    Dim cell As Range
    Dim lastRow As Long

    lastRow = Range(column & Rows.Count).End(xlUp).Row
    For Each cell In Range(column & "1:" & column & lastRow)
        If InStr(cell.Value, ".") <> 0 Then
          cell.Value = DateValue(RegexReplace(cell.Value, _
          "(\d{1,2})\.(\d{1,2})\.(\d{2,4})", "$2/$1/$3"))
        End If
        cell.NumberFormat = "d/m/yyyy h:mm AM/PM"
    Next    
End Sub

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

如果您需要其他格式，只需像这样更改一行:

If you need other format, just change one line like this:

 cell.NumberFormat = "d/m/yyyy h AM/PM"

我修改并使用了此答案和 查看全文