如何在动态Excel日历中基于月工作时长计算累计休假天数

问题需求 #

• 已搭建启用1904日期系统的动态Excel日历，支持负时间处理，日期随C2单元格的年份动态调整（固定53整周布局，无法用固定单元格计算）
• 需要在C7单元格实现休假天数累计计算器，规则如下：
  • 每月满足任一条件，可获得2天休假；入职满1年后，每月可获2.5天
    1. 当月工作天数≥14
    2. 当月工作总时长≥35小时

现有日历前10行表格 #

已尝试方法 #

• 组合DATE、SUM与COUNTA、COUNTIF等函数
• 尝试各类LOOKUP函数，均未实现需求

解决方案 #

方法一：适用于Excel 365/2021（支持动态数组） #

在C7单元格输入以下公式（请将$C$9替换为你的入职日期所在单元格）：

=SUM(
    BYMONTH(
        D11:D381,
        LAMBDA(month_dates,
            LET(
                month_work_days, COUNTIFS(D11:D381, ">="&MIN(month_dates), D11:D381, "<="&MAX(month_dates), H11:H381, ">0"),
                month_total_hours, SUMIFS(H11:H381, D11:D381, ">="&MIN(month_dates), D11:D381, "<="&MAX(month_dates))*24,
                eligible, IF(OR(month_work_days>=14, month_total_hours>=35), 1, 0),
                IF(eligible=1, IF(DATEDIF($C$9, DATE($C$2,1,1), "Y")>=1, 2.5, 2), 0)
            )
        )
    )
)

公式说明

1. BYMONTH：按日期列的月份自动分组，对每个月的数据单独计算
2. LET：定义变量简化逻辑，避免重复计算：
   • month_work_days：统计当月有有效工作时长（>0）的天数
   • month_total_hours：统计当月总时长并转换为小时数（原时长是日期格式，需×24）
   • eligible：判断当月是否满足休假条件
3. 最后根据入职年限判断每月应得休假天数，累加所有符合条件的月份数值

方法二：兼容旧版Excel（无动态数组支持） #

使用数组公式（输入后按Ctrl+Shift+Enter确认）：

=SUM(
    IF(
        (FREQUENCY(IF(H11:H381>0, MONTH(D11:D381)), MONTH(D11:D381))>=14)+
        (SUMIFS(H11:H381, MONTH(D11:D381), ROW(INDIRECT("1:12")))*24>=35)>=1,
        IF(DATEDIF($C$9, DATE($C$2,1,1), "Y")>=1, 2.5, 2),
        0
    )
)

公式说明

1. FREQUENCY：统计每个月的有效工作天数
2. SUMIFS：按月份统计总时长并转换为小时数
3. 通过逻辑判断筛选符合条件的月份，再根据入职年限计算累计休假天数

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