如何用Python将日期字符串转为Excel可识别的Datetime格式？

一、从Python源头解决（推荐） #

直接在导出Excel前将日期字符串转换为pandas的datetime类型，确保Excel打开即可识别：

• 步骤1：转换DataFrame中的日期列
  使用pd.to_datetime()将日-月-年格式的字符串转换为datetime类型，指定匹配格式%d-%m-%Y：

  import pandas as pd
  
  # 假设df是从SQL查询得到的DataFrame
  df['日期列名'] = pd.to_datetime(df['日期列名'], format='%d-%m-%Y')
  

• 步骤2：写入Excel时指定日期格式
  使用xlsxwriter引擎写入，同时设置Excel端的日期显示格式，确保兼容性：

  # 初始化Excel写入器
  writer = pd.ExcelWriter('输出文件.xlsx', engine='xlsxwriter')
  df.to_excel(writer, sheet_name='Sheet1', index=False)
  
  # 获取工作簿和工作表对象
  workbook = writer.book
  worksheet = writer.sheets['Sheet1']
  
  # 定义Excel日期显示格式（可按需调整为'短日期'等）
  date_format = workbook.add_format({'num_format': 'yyyy-mm-dd'})
  # 假设日期列是C列，设置列宽并应用格式
  worksheet.set_column('C:C', 15, date_format)
  
  writer.close()
  

二、已导出Excel文件的批量修复（VBA方法） #

如果已经生成问题文件，可通过VBA批量转换日期字符串，无需逐个双击单元格：

Sub 批量转换文本为日期()
    Dim 目标列 As Range
    Dim 单元格 As Range
    Dim 最后行 As Long
    
    ' 替换为你的日期列（示例为C列）和工作表名
    With ThisWorkbook.Sheets("Sheet1")
        最后行 = .Cells(.Rows.Count, "C").End(xlUp).Row
        Set 目标列 = .Range("C2:C" & 最后行) ' 跳过表头从第2行开始
    End With
    
    For Each 单元格 In 目标列
        If 单元格.Value <> "" Then
            ' 按日-月-年拆分字符串，转换为Excel日期
            单元格.Value = DateSerial(Mid(单元格.Value, 7, 4), Mid(单元格.Value, 4, 2), Left(单元格.Value, 2))
            ' 设置日期显示格式
            单元格.NumberFormat = "yyyy-mm-dd"
        End If
    Next 单元格
End Sub

补充说明 #

双击单元格生效的原因是Excel会在编辑时自动触发文本到日期的解析，但大量数据下手动操作不可行，通过上述代码可批量完成转换，彻底解决VBA中Average函数报错的问题。

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