Excel公式求助：自动计算存储服务月度计费天数结果异常

背景与现有公式 #

表格包含「Date In（入库日期）」和「Date Out（出库日期）」列，已实现正常计算总存储天数的公式（出库为空时默认在库，计算至当日）：

=IF(OR([@[Date In]]="",[@[Date In]] > TODAY()),"",IF([@[Date Out]] = "",TODAY()-[@[Date In]],[@[Date Out]]-[@[Date In]] + 1))

需求：指定月份计费天数计算 #

需计算单元格$M$2指定月份的存储计费天数，规则如下：

• 若出库日期为空，默认物品仍在库
• 入库日期早于指定月份：计整月天数
• 入库日期在指定月份：计入库日至月末的天数
• 用MAX(0)避免负数结果

原尝试的公式如下，但存在部分结果异常：

=IF(OR(ISBLANK([@[Date In]]), MONTH([@[Date In]]) > MONTH($M$2)),"",
MAX(0,IF(ISBLANK([@[Date Out]]),IF(MONTH([@[Date In]]) = MONTH($M$2), DAYS(EOMONTH($M$2,0),[@[Date In]]),
IF(MONTH([@[Date In]]) < $M$2, DAY(EOMONTH($M$2,0)), EOMONTH($M$2,0) - DAY([@[Date In]]))),
IF(MONTH([@[Date In]]) = MONTH([@[Date Out]]), [@[Date Out]] - [@[Date In]] + 1, IF([@[Date Out]] > EOMONTH($M$2,0), DAYS(EOMONTH([@[Date In]],0),[@[Date In]]),[@[Date Out]] - $M$2 + 1))))) 

错误示例 #

• 入库：01/02/2025，出库：05/03/2025，计费月份：03/25，返回值：5（正确）
• 入库：02/03/2025，出库：05/03/2025，计费月份：03/25，返回值：4（正确）
• 入库：02/01/2025，出库：空，计费月份：03/25，返回值：31（正确）
• 入库：02/02/2025，出库：01/04/2025，计费月份：03/25，返回值：26（错误，应计31天）
• 入库：01/02/2025，出库：05/03/2025，计费月份：10/25，返回值：0（正确）
• 入库：02/03/2025，出库：05/03/2025，计费月份：10/25，返回值：4（错误，应返回0）

问题根源分析 #

1. 忽略年份判断：仅用MONTH()函数比较月份，未结合年份。比如示例6中，入库出库为2025年3月，计费月份为2025年10月，虽然月份数值3<10，但物品早已出库，不在10月存储，却错误计算了天数。
2. 跨月逻辑错误：示例4中，原公式在判断出库日期晚于指定月末时，错误使用了入库月份的月末，而非指定月份的月末，导致计算结果偏差。
3. 缺失出库早于指定月份的判断：当出库日期早于指定月份时，物品已不在库，应返回0，但原公式未处理该边界条件。

修正后的公式 #

采用周期交集计算的逻辑，更清晰且覆盖所有边界条件：

=IF(OR([@[Date In]]="", [@[Date In]] > EOMONTH($M$2,0)), "",
LET(
    StartOfMonth, DATE(YEAR($M$2), MONTH($M$2), 1),
    EndOfMonth, EOMONTH($M$2, 0),
    ItemStart, [@[Date In]],
    ItemEnd, IF(ISBLANK([@[Date Out]]), TODAY(), [@[Date Out]]),
    OverlapStart, MAX(ItemStart, StartOfMonth),
    OverlapEnd, MIN(ItemEnd, EndOfMonth),
    MAX(0, OverlapEnd - OverlapStart + 1)
))

公式逻辑说明 #

1. 前置判断：若入库日期为空，或入库日期晚于指定月份月末，返回空值。
2. 定义变量：
   • StartOfMonth：指定月份的第一天
   • EndOfMonth：指定月份的最后一天
   • ItemStart：物品入库日期
   • ItemEnd：物品实际出库日期（为空则取当日）
3. 计算交集：
   • OverlapStart：取入库日期与指定月首的较晚值
   • OverlapEnd：取出库日期与指定月末的较早值
4. 计算天数：若交集开始晚于结束则返回0，否则返回交集天数（包含首尾）。

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