基于多日期条件计算工时的Excel公式优化需求

问题 #

要计算项目Initiation、Planning、Execution、Close各阶段的工时，每个阶段都有明确的起止日期。当前使用的SUMPRODUCT公式未考虑跨月场景，计算结果与手动核算的EXPECTED HRS列预期值不符，需修正公式。

修正后的公式（适配跨月场景） #

假设你的表格结构为：

• A列：阶段名称
• B列：阶段开始日期
• C列：阶段结束日期
• E1单元格：每日标准工时（例：8小时）

方案1：逐日期拆分计算 #

=SUMPRODUCT(NETWORKDAYS(MAX(B2,DATE(YEAR(B2),MONTH(ROW(INDIRECT(B2&":"&C2))),1)),MIN(C2,EOMONTH(ROW(INDIRECT(B2&":"&C2)),0)))*$E$1)

方案2：按月份批量拆分（计算效率更高） #

=SUMPRODUCT(NETWORKDAYS(MAX(B2,DATE(YEAR(B2),MONTH(B2)+ROW($1:$DATEDIF(B2,C2,"m")+1)-1,1)),MIN(C2,EOMONTH(B2,ROW($1:$DATEDIF(B2,C2,"m")+1)-1)))*$E$1)

公式说明 #

1. 拆分跨月区间：通过DATEDIF或ROW(INDIRECT(...))把跨月的阶段日期拆成单个自然月的子区间
2. 锁定子区间实际起止：用MAX取阶段开始日期与当月第一天的较大值，MIN取阶段结束日期与当月最后一天的较小值，确保只计算阶段覆盖的当月日期
3. 计算工作日工时：NETWORKDAYS统计子区间内的工作日数，乘以每日标准工时后，用SUMPRODUCT汇总所有子区间的工时
4. 可选：排除节假日：给NETWORKDAYS添加第三个参数即可，例：NETWORKDAYS(..., ..., $F$1:$F$10)，其中F列为节假日列表

验证方法 #

将公式应用到数据列，对比计算结果与EXPECTED HRS列的手动核算值，完全匹配则说明公式有效。

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