Excel序列号日期转换函数异常排查：Serial Date转指定格式失效

我写了一个基于Pandas的函数，用来将指定日期列的所有日期转换为指定格式，缺失或无效日期可替换为用户指定值。该函数本应支持处理如“5679”这类Excel序列号日期，但实际运行时，输入序列号日期（例如45678）会返回NaN，而非预期的2024-06-27。

我的代码 #

import pandas as pd
import math

def date_fun(df, date_inputs):
    col_name = date_inputs["DateColumn"]
    date_format = date_inputs["DateFormat"]
    replace_value = date_inputs.get("ReplaceDate", None)
    
    # Convert column values to string
    df[col_name] = df[col_name].astype(str)
    
    # Check if the column contains serial dates
    if df[col_name].str.isnumeric().all():
        # Convert the column to integer
        df[col_name] = pd.to_numeric(df[col_name], errors='coerce')
        
        # Check if the values are within the valid range of serial dates in Excel
        if df[col_name].between(1, 2958465).all():
            df[col_name] = pd.to_datetime(df[col_name], unit='D', errors='coerce')
        else:
            if replace_value is not None:
                df[col_name] = replace_value
            else:
                df[col_name] = "Invalid Date"
    else:
        df[col_name] = pd.to_datetime(df[col_name], errors='coerce')
    
    # Convert the datetime values to the specified format
    df[col_name] = df[col_name].dt.strftime(date_format)
    
    # Replace invalid or null dates with the specified value (if any)
    if replace_value is not None:
        replace_value = str(replace_value) # convert to string
        df[col_name] = df[col_name].fillna(replace_value)
    
    new_data = df[col_name].to_dict()
    
    # Handle NaN and infinity values
    def handle_nan_inf(val):
        if isinstance(val, float) and (math.isnan(val) or math.isinf(val)):
            return str(val)
        else:
            return val
    
    new_data = {k: handle_nan_inf(v) for k, v in new_data.items()}
    
    return new_data

测试场景 #

• 单个输入示例：输入45678，预期输出2024-06-27，实际输出NaN
• 批量测试输入：

25.09.2019
9/16/2015
10.12.2017
02.12.2014
08-Mar-18
08-12-2016
26.04.2016
05-03-2016
24.12.2016
10-Aug-19
abc
05-06-2015
12-2012-18
24-02-2010
2008,13,02
16-09-2015
23-01-1992, 7:45

2nd December 2018
45678

• 指定日期格式："%Y/%m/%d"

当前输出 #

"2019/09/25",
            "2015/09/16",
            "2017/10/12",
            "2014/02/12",
            "2018/03/08",
            "2016/08/12",
            "2016/04/26",
            "2016/05/03",
            "2016/12/24",
            "2019/08/10",
            "nan",
            "2015/05/06",
            "nan",
            "2010/02/24",
            "2008/02/01",
            "2015/09/16",
            "1992/01/23",
            "nan",
            "2018/12/02",
            "nan"

问题原因分析 #

1. 整列判断逻辑失效：代码中df[col_name].str.isnumeric().all()要求整列所有值都是纯数字字符串才会触发序列号转换分支，但测试数据混合了普通日期、无效值和序列号日期，导致该条件不成立，序列号日期被丢入普通转换分支，而pd.to_datetime默认不会将纯数字字符串识别为Excel序列号，因此返回NaN。
2. 日期基准偏移：即使触发了序列号转换分支，pd.to_datetime的unit='D'默认以1970-01-01为基准，而Excel序列号的基准是1899-12-30（兼容1900年闰年bug），直接使用会导致日期计算错误。

修复方案 #

修改逻辑为逐值判断转换：先尝试普通日期转换，失败后再检查是否为有效Excel序列号，同时修正日期基准。修复后的代码如下：

import pandas as pd
import math

def date_fun(df, date_inputs):
    col_name = date_inputs["DateColumn"]
    date_format = date_inputs["DateFormat"]
    replace_value = date_inputs.get("ReplaceDate", None)
    
    def convert_date(val):
        # 优先尝试普通日期转换
        dt = pd.to_datetime(val, errors='coerce')
        if pd.notna(dt):
            return dt
        # 普通转换失败，尝试解析为Excel序列号
        try:
            serial = float(val)
            # 校验Excel序列号有效范围（1900-01-01 至 9999-12-31）
            if 1 <= serial <= 2958465:
                # 使用Excel的基准日期处理序列号
                dt = pd.to_datetime(serial, unit='D', origin='1899-12-30')
                return dt
            else:
                return pd.NaT
        except (ValueError, TypeError):
            return pd.NaT
    
    # 对列中每个值应用转换逻辑
    df[col_name] = df[col_name].apply(convert_date)
    
    # 转换为指定格式，无效日期转为NaN
    df[col_name] = df[col_name].dt.strftime(date_format)
    
    # 替换无效值
    if replace_value is not None:
        df[col_name] = df[col_name].fillna(str(replace_value))
    else:
        df[col_name] = df[col_name].fillna("Invalid Date")
    
    # 处理剩余的NaN/inf值
    new_data = df[col_name].to_dict()
    def handle_nan_inf(val):
        if isinstance(val, float) and (math.isnan(val) or math.isinf(val)):
            return str(val)
        else:
            return val
    new_data = {k: handle_nan_inf(v) for k, v in new_data.items()}
    
    return new_data

修复后效果 #

测试输入中的45678会被正确转换为2024/06/27，符合指定格式要求；其他混合日期可正常解析，无效值会被替换为指定内容或默认的"Invalid Date"。

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