使用Excel按30分钟间隔统计可用人数，添加日期条件遇#N/A错误

嘿，我来帮你解决这个头疼的#N/A错误问题！先拆解下问题出在哪，再给你直接能用的修正公式。

为啥会出#N/A？ #

你的公式踩了两个关键坑：

• 日期格式不兼容：你用文本字符串"1/1/2019"和C列的日期单元格比较，如果C列存的是Excel标准日期值（不是纯文本），这种跨类型对比就会触发#N/A错误。
• 跨天班次逻辑混乱：原公式把日期匹配和班次时间判断的逻辑混在一起，数组运算的条件组合不符合规则，导致统计出错。

修正后的实用公式 #

先明确数据格式假设：

• A列：开始时间（仅时间，如17:30）
• B列：结束时间（仅时间，如02:30）
• C列：班次所属日期（Excel标准日期格式，如2019/1/1）
• D2：要统计的30分钟间隔起始时间（如00:00、00:30）

固定日期统计（比如仅统计2019年1月1日）

=SUMPRODUCT(
    --(C$2:C$1000=DATE(2019,1,1)),
    --(
        // 未跨天班次：目标时间在班次范围内
        (A$2:A$1000<=B$2:B$1000)*(A$2:A$1000<=D2)*(B$2:B$1000>D2)
        +
        // 跨天班次（如17:30到次日02:30）：目标时间在当天后半段或次日凌晨
        (A$2:A$1000>B$2:B$1000)*( (A$2:A$1000<=D2)+(B$2:B$1000>D2) )
    )
)

动态日期统计（用单元格存目标日期，比如E2）

如果不想每次改日期都修改公式，换成单元格引用即可：

=SUMPRODUCT(
    --(C$2:C$1000=E2),
    --(
        (A$2:A$1000<=B$2:B$1000)*(A$2:A$1000<=D2)*(B$2:B$1000>D2)
        +
        (A$2:A$1000>B$2:B$1000)*( (A$2:A$1000<=D2)+(B$2:B$1000>D2) )
    )
)

公式逻辑说明 #

• 日期匹配更靠谱：用DATE(2019,1,1)生成Excel认可的标准日期值，彻底避免格式不匹配问题；如果用单元格引用，确保该单元格是标准日期格式就行。
• 跨天班次统计准确：
  • 未跨天班次：只要目标时间在开始和结束时间之间，就算该人员可用
  • 跨天班次：比如当天17:30到次日02:30，只要目标时间是当天17:30之后，或是次日00:00到02:30之间，都算可用
• --的作用：把布尔值（TRUE/FALSE）转换成1/0，让SUMPRODUCT能顺利对符合条件的人数求和

测试验证 #

用你提供的测试数据，统计2019/1/1的17:30间隔，正确结果应为17人（2个17:30-02:30 + 3个16:00-01:00 + 5个15:00-00:00 + 1个15:00-22:00 + 6个14:30-18:30），上述公式能算出正确数值。

内容的提问来源于stack exchange，提问作者Rameez Raja M