Excel日期格式转换求助：如何通过VBA实现文本型日期转可识别格式

问题根源 #

你用=Format(Now(), "dd/mm/yyyy")生成的是文本型日期，不是Excel能识别的日期数值，所以其他公式无法正常读取。要彻底解决，要么直接输入日期数值，要么批量转换已有的文本日期。

一、直接输入可识别的日期（替代原公式） #

如果要在单元格插入当日日期且确保是可识别的数值，用以下VBA代码：

1. 手动触发插入当日日期 #

Sub InsertTodayDate()
    ' 向选中单元格插入当日日期（数值型）并设置显示格式
    If Not Selection Is Nothing Then
        Selection.Value = Date ' Date返回系统当前日期，是纯数值
        Selection.NumberFormat = "dd/mm/yyyy" ' 设置显示格式为日/月/年
    End If
End Sub

可以把这个代码绑定到工作表按钮，点击直接插入。

2. 批量替换现有Format公式 #

如果工作表里已经有大量=Format(Now(), "dd/mm/yyyy")公式，替换成返回日期数值的公式：

Sub ReplaceFormatWithDate()
    Dim targetSheet As Worksheet
    Set targetSheet = ThisWorkbook.Worksheets("Sheet1") ' 替换为你的工作表名称
    
    ' 替换公式为=TODAY()（返回日期数值）
    targetSheet.Cells.Replace What:="=Format(Now(), ""dd/mm/yyyy"")", _
                              Replacement:="=TODAY()", _
                              LookAt:=xlWhole
    
    ' 统一设置日期显示格式
    targetSheet.UsedRange.SpecialCells(xlCellTypeFormulas, xlNumbers).NumberFormat = "dd/mm/yyyy"
End Sub

二、批量转换已存在的文本型日期 #

如果你已经有一堆文本格式的日期，用VBA模拟「文本分列-完成」的操作，或者直接转换：

1. 模拟文本分列操作（和手动操作效果完全一致） #

Sub ConvertTextDatesWithTextToColumns()
    Dim targetRange As Range
    
    ' 选择要转换的文本日期区域，这里用工作表已使用区域的文本单元格
    ' 可替换为具体范围，比如Range("A2:A1000")
    On Error Resume Next ' 防止无文本单元格时报错
    Set targetRange = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, xlTextValues)
    On Error GoTo 0
    
    If Not targetRange Is Nothing Then
        ' 执行文本分列：无分隔符，按日/月/年格式识别日期
        targetRange.TextToColumns Destination:=targetRange, _
                                  DataType:=xlDelimited, _
                                  FieldInfo:=Array(1, xlDMYFormat)
        
        ' 统一设置显示格式
        targetRange.NumberFormat = "dd/mm/yyyy"
    End If
End Sub

2. 直接转换文本为日期值 #

如果文本日期格式统一为dd/mm/yyyy，可以用CDate函数直接转换：

Sub ConvertTextToDateValue()
    Dim cell As Range
    Dim targetRange As Range
    
    On Error Resume Next
    Set targetRange = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, xlTextValues)
    On Error GoTo 0
    
    If Not targetRange Is Nothing Then
        For Each cell In targetRange
            ' 仅转换合法的日期文本
            If IsDate(cell.Value) Then
                cell.Value = CDate(cell.Value)
                cell.NumberFormat = "dd/mm/yyyy"
            End If
        Next cell
    End If
End Sub

注意事项 #

• 如果你系统的日期默认格式是mm/dd/yyyy，要确保xlDMYFormat参数正确，否则会识别错误；
• 优先用「直接输入日期数值」的方案，从根源避免生成文本日期，减少后续转换工作。

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