核心问题拆解 #

你遇到的问题是：

• 合法英国格式日期（25/10/2024）已被Excel识别，但带点的格式（25.5.2024）无法识别
• 直接用SUBSTITUTE会把已识别的日期转成序列号（如45058）
• 需要处理n/a和空白值
• 无ISDATE()函数可用

分步解决方案 #

1. 通用公式（兼容新旧Excel版本） #

方法一：适用于Excel 365/2021（支持TEXTSPLIT和LET）

这个公式能同时处理带.和/的文本日期，保留已识别的日期，过滤n/a和空白：

=IF(OR(X2="n/a", X2=""), "", IF(ISNUMBER(X2), X2, LET(
    split_text, TEXTSPLIT(SUBSTITUTE(X2, ".", "/"), "/"),
    DATE(INDEX(split_text, 3), INDEX(split_text, 2), INDEX(split_text, 1))
)))

逻辑说明：

• 先把所有.替换为/，统一分隔符
• 用TEXTSPLIT拆分出日、月、年（英国格式顺序）
• 用DATE函数按「年-月-日」组合成标准日期
• 跳过n/a和空白，直接保留已识别的日期（数字类型）

方法二：兼容旧版Excel（无TEXTSPLIT/LET）

如果你的Excel版本不支持新函数，用以下公式：

=IF(OR(X2="n/a", X2=""), "", IF(ISNUMBER(X2), X2, DATE(
    RIGHT(SUBSTITUTE(X2, ".", "/"), 4),
    MID(SUBSTITUTE(X2, ".", "/"), FIND("/", SUBSTITUTE(X2, ".", "/"))+1, FIND("/", SUBSTITUTE(X2, ".", "/"), FIND("/", SUBSTITUTE(X2, ".", "/"))+1)-FIND("/", SUBSTITUTE(X2, ".", "/"))-1),
    LEFT(SUBSTITUTE(X2, ".", "/"), FIND("/", SUBSTITUTE(X2, ".", "/"))-1)
)))

逻辑说明：

• 同样先统一替换分隔符为/
• 用LEFT/MID/RIGHT拆分日、月、年，再用DATE组合

2. 避免序列号问题的关键 #

直接用SUBSTITUTE处理已识别的日期会转成序列号，是因为Excel的日期本质是数字（序列号），SUBSTITUTE作为文本函数会把数字转成文本形式的序列号。所以必须通过ISNUMBER(X2)先判断：如果是数字（已识别的日期），直接保留；仅对文本类型的内容做替换拆分。

3. 批量操作步骤 #

1. 在空白列（比如Y列）的Y2单元格输入上述公式
2. 下拉填充整列，完成所有行的转换
3. 选中Y列，右键选择「复制」，再选中原日期列（X列），右键选择「粘贴选项」→「值」，替换原数据

4. 区域设置兼容 #

如果你的Excel区域是美国格式（mm/dd/yyyy），直接用DATEVALUE会错误识别英国格式日期（比如25/10/2024会被判定为无效）。上面的拆分方法完全规避了这个问题，因为明确指定了日、月、年的顺序。

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