Excel公式: Overlapping Dates in table

Question

我正在做一个 excel 任务，我必须找出是否有重叠的日期。

在我的 excel 工作表中有 startDate(column D) 和 EndDate(comun E) 的列，由此我必须确定是否存在重叠日期。

以下公式确定这组 startDate 和 endDate 是否与工作表中的任何其他日期组合重叠。

=SUM(COUNTIFS($A$2:$A$100,$A2,$D$2:$D$100,"<"&$D2)-COUNTIFS($A$2:$A$100,$A2,$D$2:$D$100,"<"&$D2,$E$2:$E$100,"<"&$D2),COUNTIFS($A$2:$A$100,$A2,$E$2:$E$100,">"&$E2)-COUNTIFS($A$2:$A$100,$A2,$E$2:$E$100,">"&$E2,$D$2:$D$100,">"&$E2),IF(COUNTIFS($A$2:$A$100,$A2,$D$2:$D$100,$D2)=1,0,1),IF(COUNTIFS($A$2:$A$100,$A2,$E$2:$E$100,$E2)=1,0,1))>0

当此公式应用于所有行时，无效行在 InvalidDate 列中显示为真值。这些值的逻辑与给我最终的预期值，表明是否存在任何无效日期。

我的 excel 列如下所示:

Name(A)   SD(D)   ED(E) InvalidDate
B   01-03-14    04-03-14    TRUE
B   06-03-14    07-03-14    FALSE
B   05-03-14    05-03-14    FALSE
B   05-02-14    05-02-14    FALSE
B   03-03-14    03-03-14    TRUE
B   17-03-14    18-03-14    FALSE
B   21-02-14    02-03-14    TRUE
B   08-03-14    09-03-14    FALSE
B   10-03-14    15-03-14    FALSE
B   16-03-14    16-03-14    FALSE

SD: Start Date
ED: End Date
Name: Value in Column A (Dates are compared for identical values of this column)

但是，我不能有额外的专栏。因此，我需要修改公式，使其返回 TRUE，前提是所有日期值都有效。

如果不使用该列，我无法弄清楚如何进行逻辑与运算。感谢您的想法和努力。

Answer 1

用于计算单行重叠日期范围的公式可以简化:

=COUNTIFS($E$2:$E$11,">="&D2, $D$2:$D$11,"<="&E2, $A$2:$A$11,A2)

要将其更改为 bool 值，请添加 >1在最后。

要检查单个单元格中的所有行，您可以将聚合函数与上述公式的数组版本一起使用。例如(使用 Ctrl+Shift+Enter 确认):

=MAX(COUNTIFS($E$2:$E$11,">="&$D$2:$D$11,$D$2:$D$11,"<="&$E$2:$E$11,$A$2:$A$11,$A$2:$A$11))