如何用Excel公式计算两日期间每月特定日期的事件发生次数？

针对你需要计算两个日期之间每月15日的事件发生次数的需求，这里提供一个通用的Excel公式，同时拆解逻辑确保你理解每一部分：

核心公式 #

假设起始日期存放在单元格A1，结束日期存放在单元格B1，公式如下：

=MAX(0, IF((IF(DAY(A1)<=15, DATE(YEAR(A1), MONTH(A1), 15), DATE(YEAR(A1), MONTH(A1)+1, 15))) > (IF(DAY(B1)>=15, DATE(YEAR(B1), MONTH(B1), 15), DATE(YEAR(B1), MONTH(B1)-1, 15))), 0, DATEDIF(IF(DAY(A1)<=15, DATE(YEAR(A1), MONTH(A1), 15), DATE(YEAR(A1), MONTH(A1)+1, 15)), IF(DAY(B1)>=15, DATE(YEAR(B1), MONTH(B1), 15), DATE(YEAR(B1), MONTH(B1)-1, 15)), "m") + 1))

公式逻辑拆解 #

1. 确定第一个有效事件日

   • 如果起始日期的日数≤15，当月15日就是第一个需要统计的事件日；
   • 如果起始日期的日数>15，当月15日已过，第一个事件日为次月15日。

2. 确定最后一个有效事件日

   • 如果结束日期的日数≥15，当月15日是最后一个需要统计的事件日；
   • 如果结束日期的日数<15，当月15日未到，最后一个事件日为上月15日。

3. 计算事件次数

   • 若第一个事件日晚于最后一个事件日，说明区间内无事件，返回0；
   • 否则用DATEDIF计算两个事件日的月份间隔，再加1（因为DATEDIF返回的是间隔月份数，比如2月15到3月15间隔1个月，实际对应2次事件）；
   • 用MAX(0, ...)确保结果不会出现负数（比如起始日期晚于结束日期的情况）。

验证你的示例 #

当A1=2024-01-16，B1=2024-03-16时：

• 第一个事件日：2024-02-15（起始日16>15，取次月15日）
• 最后一个事件日：2024-03-15（结束日16≥15，取当月15日）
• DATEDIF(2024-02-15, 2024-03-15, "m")返回1，加1后得到2，与预期结果一致。

其他测试场景 #

• 起始日=结束日=2024-01-15：返回1（当天事件统计在内）
• 起始日=2024-01-14，结束日=2024-01-16：返回1（1月15日的事件在区间内）
• 起始日=2024-03-16，结束日=2024-03-16：返回0（当月15日已过，次月15日不在区间内）

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