如何在VBA中将Excel文本时间Fri Mar 31 14:37:20 GMT 2023转为DD/MM/YYYY

方法1：利用内置日期转换函数 #

VBA的CDate函数可直接识别Fri Mar 31 14:37:20 GMT 2023这类格式的字符串，结合Format函数快速转换为欧洲日期格式：

Sub ConvertSingleGMTToEU()
    Dim rawText As String
    Dim dateVal As Date
    Dim euDate As String
    
    ' 替换为目标单元格引用，比如Range("A1").Value
    rawText = "Fri Mar 31 14:37:20 GMT 2023"
    
    ' 转换为日期类型
    dateVal = CDate(rawText)
    ' 格式化为DD/MM/YYYY
    euDate = Format(dateVal, "dd/mm/yyyy")
    
    ' 将结果写入目标单元格，比如Range("B1").Value = euDate
    Debug.Print euDate ' 输出：31/03/2023
End Sub

方法2：手动拆分字符串（兼容性更强） #

如果系统区域设置无法识别英文月份缩写，可通过拆分字符串+字典映射处理，避免转换报错：

Sub SplitAndConvertGMT()
    Dim rawText As String
    Dim textParts() As String
    Dim monthDict As Object
    Dim day As String, monthNum As Integer, year As String
    Dim euDate As String
    
    rawText = "Fri Mar 31 14:37:20 GMT 2023"
    Set monthDict = CreateObject("Scripting.Dictionary")
    
    ' 建立英文月份缩写到数字的映射
    monthDict("Jan") = 1
    monthDict("Feb") = 2
    monthDict("Mar") = 3
    monthDict("Apr") = 4
    monthDict("May") = 5
    monthDict("Jun") = 6
    monthDict("Jul") = 7
    monthDict("Aug") = 8
    monthDict("Sep") = 9
    monthDict("Oct") = 10
    monthDict("Nov") = 11
    monthDict("Dec") = 12
    
    ' 按空格拆分字符串
    textParts = Split(rawText, " ")
    ' 提取日期核心部分：月份缩写、日、年
    day = textParts(2)
    monthNum = monthDict(textParts(1))
    year = textParts(5)
    
    ' 组合并格式化为目标格式
    euDate = Format(DateSerial(year, monthNum, day), "dd/mm/yyyy")
    
    Debug.Print euDate ' 输出：31/03/2023
End Sub

批量处理单元格数据 #

如果需要处理整列的时间字符串，使用以下批量转换代码：

Sub BatchConvertGMTColumn()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim rawText As String
    Dim dateVal As Date
    
    ' 替换为你的工作表名称
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    ' 获取A列最后一行数据的行号
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    For i = 1 To lastRow
        rawText = ws.Cells(i, "A").Value
        If rawText <> "" Then
            On Error Resume Next ' 跳过无法识别的格式
            dateVal = CDate(rawText)
            If Err.Number = 0 Then
                ' 将结果写入B列，同时设置单元格格式为日期
                ws.Cells(i, "B").Value = dateVal
                ws.Cells(i, "B").NumberFormat = "dd/mm/yyyy"
            Else
                ws.Cells(i, "B").Value = "格式无效"
            End If
            On Error GoTo 0
        End If
    Next i
End Sub

注意事项 #

• 若需保留时间信息，可将Format参数改为"dd/mm/yyyy hh:mm:ss"
• 批量处理的错误处理可避免单个无效格式导致程序中断
• 方法2的字典映射不受系统区域设置影响，兼容性更强

内容的提问来源于stack exchange，提问作者AlexandraStk