Excel日期和时间转换

这是VBA中的代码段，可将阿塞拜疆日期格式转换m.d.y为ar-SA日期格式d/m/y。运行FixDates带有参数“ A”的应将文本转换为A列中所有单元格m.d.yy hh:mm:ss的日期值d/m/yyyy hh:mm:ss。它使用VBScript类中的RegExp。

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

如果您需要其他格式，只需更改一行，如下所示：

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

我已经修改并使用了这个答案和这个答案。