如何将Google Sheets中IMPORTXML提取的日期转为可用于条件格式的日期值？

核心问题 #

提取的日期字符串带有序数后缀（如th），导致DATEVALUE无法识别为有效日期。

解决方案 #

1. 清理日期后缀并转换为日期值 #

使用REGEXREPLACE移除日期中的序数后缀（st/nd/rd/th），再用DATEVALUE转换：

=DATEVALUE(REGEXREPLACE(MID(C2, 1, FIND(",", C2) - 1), "(\d+)(st|nd|rd|th)", "$1"))

• 逻辑：MID提取逗号前的日期部分，REGEXREPLACE把数字后的序数后缀替换为空，得到标准日期字符串（如October 6 2023），最后DATEVALUE将其转为可识别的日期值。

2. 转换为完整日期时间值（含时间） #

如果需要保留时间信息，可组合DATEVALUE和TIMEVALUE：

=DATEVALUE(REGEXREPLACE(MID(C2, 1, FIND(",", C2) - 1), "(\d+)(st|nd|rd|th)", "$1")) + TIMEVALUE(MID(C2, FIND(",", C2) + 2, LEN(C2)))

• 结果会生成带时间的日期时间值，支持后续的时间计算和格式设置。

3. 条件格式设置（标记超1个月内容） #

假设转换后的日期在D列，按以下步骤设置：

• 选中目标单元格区域
• 点击「格式」→「条件格式」
• 选择「自定义公式」，输入：

  =DATEDIF(D2, TODAY(), "M") > 1
  

• 设置填充色或字体色为红色，完成规则配置。

4. 一步到位提取并转换（可选） #

若不想单独存储原始提取内容，可直接在IMPORTXML后链式处理（注意：会执行两次IMPORTXML，数据量大时效率较低）：

=DATEVALUE(REGEXREPLACE(MID(IMPORTXML(B2, $K$2), 1, FIND(",", IMPORTXML(B2, $K$2)) - 1), "(\d+)(st|nd|rd|th)", "$1"))

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