excel - 使用 Excel 以 30 分钟间隔计算总计划

Question

我正在尝试找到一种方法来根据他们的开始时间和结束时间计算一天中每 30 分钟间隔有多少人可用，但我无法将日期条件添加到公式中（添加另一个条件时对于下面提到的公式，我得到#NA

我尝试了以下公式 =SUMPRODUCT(((--(C$2:C$1000="1/1/2019")*(A$2:A$1000<=D2)+(B$2:B$1000>D2)+( A$2:A$1000>B$2:B$1000)=2)+0))

+-------+-------+------------+
| Start |  End  |    Date    |
+-------+-------+------------+
| 17:30 | 02:30 | 02/01/2019 |
| 17:30 | 02:30 | 02/01/2019 |
| 17:30 | 02:30 | 02/01/2019 |
| 17:30 | 02:30 | 02/01/2019 |
| 17:30 | 02:30 | 01/01/2019 |
| 17:30 | 02:30 | 01/01/2019 |
| 16:00 | 01:00 | 01/01/2019 |
| 16:00 | 01:00 | 01/01/2019 |
| 16:00 | 01:00 | 01/01/2019 |
| 15:00 | 00:00 | 02/01/2019 |
| 15:00 | 00:00 | 02/01/2019 |
| 15:00 | 00:00 | 02/01/2019 |
| 15:00 | 00:00 | 02/01/2019 |
| 15:00 | 00:00 | 02/01/2019 |
| 15:00 | 00:00 | 02/01/2019 |
| 15:00 | 00:00 | 01/01/2019 |
| 15:00 | 00:00 | 01/01/2019 |
| 15:00 | 00:00 | 01/01/2019 |
| 15:00 | 00:00 | 01/01/2019 |
| 15:00 | 00:00 | 01/01/2019 |
| 15:00 | 22:00 | 01/01/2019 |
| 14:30 | 18:30 | 01/01/2019 |
| 14:30 | 18:30 | 01/01/2019 |
| 14:30 | 18:30 | 01/01/2019 |
| 14:30 | 18:30 | 01/01/2019 |
| 14:30 | 18:30 | 01/01/2019 |
| 14:30 | 18:30 | 01/01/2019 |
+-------+-------+------------+

标签： excel

Answer 1

好的，这是我的公式和结果，完全不同：

=SUMPRODUCT(($C$2:$C$28+$A$2:$A$28<=G$1+$F2)*($C$2:$C$28+$B$2:$B$28+($B$2:$B$28<$A$2:$A$28)>=G$1+$F2))

这只是将开始日期 + 时间与给定日期 + 时间进行比较，结束日期 + 时间也与给定日期 + 时间进行比较。如果班次经过午夜，则结束日期会增加一天。

我已经对数据进行了排序，因为它更容易检查，并使用 1 小时的增量来测试它：

这对我来说很有意义，因为（例如）在第一天直到 15:00 之前什么都没有发生，但是其中一些人仍在午夜工作，所以在第二天算上。