求可识别两组跨列日期范围间隙的Excel公式

核心需求 #

验证C:D列的合同日期范围是否完全覆盖A:B列的实际工作日期范围——即实际工作的每一天都被至少一个合同日期区间包含，无遗漏间隙。以下分两种常见场景给出对应公式：

场景1：单行验证（单条工作区间对应单条合同区间） #

检查当前行的合同区间（C1:D1）是否完全覆盖该行的工作区间（A1:B1），公式直接下拉即可批量验证每行：

=AND(C1<=A1, D1>=B1)

• 返回TRUE：合同完全覆盖该条工作区间
• 返回FALSE：合同未覆盖该条工作区间（存在间隙或超出范围）

场景2：整体批量验证（所有工作区间的合并范围是否被所有合同区间完全覆盖） #

适用于A:B列存在多条不连续工作区间、C:D列存在多条合同区间的情况，需确保工作的所有日期都被至少一个合同区间包含：

Excel 365/2021（动态数组）公式：

=ALL(COUNTIFS(C:C,"<="&SEQUENCE(MAX(B:B)-MIN(A:A)+1,1,MIN(A:A)),D:D,">="&SEQUENCE(MAX(B:B)-MIN(A:A)+1,1,MIN(A:A)))>=1)

旧版Excel（需按Ctrl+Shift+Enter作为数组公式输入）：

=SUMPRODUCT(--(COUNTIFS(C:C,"<="&ROW(INDIRECT(MIN(A:A)&":"&MAX(B:B))),D:D,">="&ROW(INDIRECT(MIN(A:A)&":"&MAX(B:B))))=0))=0

公式说明：

1. 先获取工作日期的最早开始（MIN(A:A)）和最晚结束（MAX(B:B)），生成该区间内的所有日期序列
2. 逐个检查每个日期是否被至少一个合同区间包含
3. 最终返回TRUE表示所有工作日期都被覆盖，FALSE则说明存在未被覆盖的间隙

注意事项 #

• 确保A、B、C、D列均为有效日期格式，无文本型日期或空值（若存在空值，可将MIN(A:A)替换为MIN(IF(A:A<>"",A:A))，并按数组公式规则输入）
• 若需排除周末/节假日，可在生成日期序列时加入筛选逻辑（结合WORKDAY.INTL函数）

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