给定的时间间隔内有多少小时的时间范围？

Question

我想将小时数作为给定时间间隔内的时间范围进行计数。对于我来说，用英语表达我想要表达的意思有点困难，所以让我用一个例子来解释我想要什么：

  A                 B               C        D          E
1 Start of Fault    End of Fault     Time Ranges        Number of Faults
2 12:00             13:00           00:00    01:00      X
3 12:00             14:00           01:00    02:00      X
4 08:30             23:00           02:00    03:00      X
5 00:00             24:00           03:00    04:00      X
. ...               ...             ...      ...        ...

如您所见，我有故障的开始和结束时间。在 C 和 D 列中，我有从 00:00 开始到 24:00 结束的时间范围。此外，

X = How many faults did happen between a time range (00:00-01:00)?

X 必须扫描 A 和 B 列，并且如果给定的时间范围（例如 12:00-13:00）包含在任何时间间隔（例如 12:00-13:00、08:30-23:00）中，则 X 是给定时间范围与时间间隔之间的交点数。

我尝试使用以下 Excel 公式计算 X：

=IF(AND($A$2>=C2;$B$2<=D2);1;0)+IF(AND($A$3>=C2;$B$3<=D2);1;0)+IF(AND($A$4>=C2;$B$4<=D2);1;0)+IF(AND($A$5>=C2;$B$5<=D2);1;0)

但只有时间范围和时间间隔相等时，此公式才有效。

有没有办法使用 Excel 公式获取 X 的值？

先感谢您。

PS：我的母语不是英语。

Answer 1

你的公式是：

=COUNTIFS(A:A,"<"&D2,B:B,">="&C2)

您的“时间范围”的唯一问题是开始和结束时间重叠，这意味着某些值可能会被计算两次。为了解决这个问题，我选择使用A<D但B>=C。如果这不是您预期的行为，并且您希望它们在同一“分钟”内被计算两次，那么您可以更改A<D为A<=D。

输入公式E2然后填写。