Python实现Excel日期数字与多格式日期统一转换

使用OpenPyxl加载仪表板导出的xlsx文件时，FE RETA、RETA列存在三类日期数据：

• Excel日期数字（如44774.375需转为01-08-2022 09:00:00）
• MM/DD/YYYY带AM/PM格式（如4/21/2023 10:00:00 PM需转为21-04-2023 10:00:00）
• 已合规的DD-MM-YYYY格式

现有pandas代码因列内混合格式报错「TypeError: Invalid type for timedelta scalar: <class 'datetime.datetime'>」，需自动化完成统一格式转换，无法要求上传时修正数据。

现有代码：

import pandas as pd
from openpyxl import load_workbook

wb = load_workbook(filename= "file.xlsx", data_only = True)
sheet_names = wb.get_sheet_names()
name = sheet_names[0]
sheet_ranges = wb[name]
df = pd.DataFrame(sheet_ranges.values, index = None)
print(df)

#To replace (4/21/2023 10:00:00 PM) format to (21-04-2023 10:00:00)
df['FE RETA'] = pd.to_datetime(df['FE RETA'], format = '%m/%d/%Y %I:%M:%S %p', errors='coerce')
df['FE RETA'] =df['FE RETA'].dt.strftime('%d-%m-%Y %H:%m:%S')

#To replace all remaining number format to (21-04-2023 10:00:00) 
#but its only working if the entire column is in number format
df['FE RETA'] = pd.to_datetime(df['FE RETA'],unit='d') + dt.datetime(1899,12,30)
print(df)

源数据：

Tht|       FE RETA               |  RETA                | TPM SLACycletime
---------------------------------------------------------------------------
US |    4/21/2023 10:00:00 PM    |4/21/2023 10:30:00 AM | Invalid Data
US |    4/22/2023 11:45:00 PM    |44781.54167           | 558:19:30
US |    4/21/2023 10:30:00 AM    |10-09-2022 18:03:00   | 111:44:26
US |    01-08-2022 10:00:00      |44778.41667           | 15:44:26
US |    44774.375                |44775.52083           | Invalid Data

期望输出：

Tht|       FE RETA               |  RETA                | TPM SLACycletime
---------------------------------------------------------------------------
US |    21-04-2023 10:00:00      |21-04-2023 10:30:00   | Invalid Data
US |    22-04-2023 11:45:00      |08-08-2022 13:00:00   | 558:19:30
US |    21-04-2023 10:30:00      |10-09-2022 18:03:00   | 111:44:26
US |    01-08-2022 10:00:00      |05-08-2022 10:00:00   | 15:44:26
US |    01-08-2022 09:00:00      |02-08-2022 12:30:00   | Invalid Data

问题分析 #

原代码报错核心原因：

1. 第一次转换后，合规日期和Excel数字会被转为NaT，经strftime处理后变成字符串NaT，第二次处理时试图将字符串与datetime对象相加，触发类型错误。
2. 未导入datetime模块却直接使用dt.datetime，且没有区分不同类型数据的处理逻辑。

修正后的代码 #

import pandas as pd
from openpyxl import load_workbook
import datetime as dt

# 加载Excel文件，替换弃用方法
wb = load_workbook(filename="file.xlsx", data_only=True)
sheet_name = wb.sheetnames[0]
sheet = wb[sheet_name]
# 指定列名避免索引混乱
df = pd.DataFrame(sheet.values, columns=["Tht", "FE RETA", "RETA", "TPM SLACycletime"])

# 定义通用日期转换函数
def convert_date(value):
    # 处理Excel日期数字
    if isinstance(value, (int, float)):
        excel_epoch = dt.datetime(1899, 12, 30)
        return (excel_epoch + dt.timedelta(days=value)).strftime("%d-%m-%Y %H:%M:%S")
    # 处理带AM/PM的日期字符串
    try:
        dt_obj = pd.to_datetime(value, format="%m/%d/%Y %I:%M:%S %p")
        return dt_obj.strftime("%d-%m-%Y %H:%M:%S")
    except (ValueError, TypeError):
        pass
    # 验证并保留合规格式的日期
    try:
        pd.to_datetime(value, format="%d-%m-%Y %H:%M:%S")
        return value
    except (ValueError, TypeError):
        # 非日期格式返回原值，保证数据不丢失
        return value

# 对目标列批量应用转换
df["FE RETA"] = df["FE RETA"].apply(convert_date)
df["RETA"] = df["RETA"].apply(convert_date)

print(df)

代码说明 #

1. 替换弃用API：用wb.sheetnames替代已废弃的get_sheet_names()，符合openpyxl最新规范。
2. 明确列定义：创建DataFrame时指定列名，避免后续列索引错误。
3. 分层处理逻辑：
   • 优先识别数字类型的Excel日期，转换为指定格式字符串；
   • 再尝试解析带AM/PM的日期字符串，转换为目标格式；
   • 最后验证合规格式的日期，直接保留；
   • 非日期格式数据返回原值，避免数据丢失。
4. 批量处理列：通过apply对FE RETA和RETA列统一应用转换逻辑，实现格式标准化。

内容的提问来源于stack exchange，提问作者Devaraj Mani Maran