Excel 365 如何判断同一单元格内多个日期范围是否重叠

适用场景 #

• 多个日期范围存储在同一个单元格内，无需拆分到多行多列
• 每行日期段数量不固定
• 仅适用于Excel 365版本（支持动态数组函数）

通用公式 #

假设存储所有日期范围的单元格为A1，在空白单元格输入以下公式即可直接得到判断结果：

=LET(
    拆分范围, TEXTSPLIT(A1,", "),
    拆分起止, TEXTSPLIT(拆分范围," - "),
    转日期, DATEVALUE(拆分起止),
    起始日期, INDEX(转日期,,1),
    结束日期, INDEX(转日期,,2),
    排序后, SORTBY(HSTACK(起始日期,结束日期),起始日期),
    后段起始, DROP(INDEX(排序后,,1),1),
    前段结束, DROP(INDEX(排序后,,2),-1),
    重叠判断, OR(后段起始 <= 前段结束),
    IF(重叠判断,"存在日期重叠","无日期重叠")
)

公式逻辑说明 #

• 第一步先按, 拆分单元格内容，得到每一组独立的日期范围
• 第二步按-拆分每组日期范围，得到单独的起始、结束日期文本
• 用DATEVALUE将日期文本转换为Excel可计算的日期序列号
• 把所有日期范围按起始日期升序排序
• 依次比较相邻两个日期范围：只要后一个范围的起始日期早于等于前一个范围的结束日期，即判定为存在重叠
• 最终返回对应的判断结果

示例验证 #

对于示例单元格内容：

4/20/2021 - 5/21/2021, 4/20/2021 - 5/20/2021, 4/26/2021 - 4/27/2021, 4/29/2021 - 5/4/2021

代入公式后将返回存在日期重叠，符合实际情况。

注意事项 #

• 如果你的日期范围分隔符不是, 、或者起止日期间隔符不是-，修改TEXTSPLIT函数中对应的分隔符参数即可
• 中文格式的日期（如2021年4月20日、2021/4/20）可被DATEVALUE自动识别，无需额外调整
• 公式支持任意数量的日期范围，只要符合统一的分隔符规则即可

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