Excel日期分类与公休日应付工时公式求助：PH分类公式失效

1. 修复「Date category」列的PH标识失效问题 #

原公式无法识别公休日，大概率是日期格式不匹配或查找范围引用逻辑偏差，替换为以下公式即可解决：

=IF(ISNUMBER(MATCH(B54, $H$52:$I$57, 0)), "PH", IF(WEEKDAY(B54, 2) > 5, "WE", "WD"))

• 用MATCH替代COUNTIF：更精准匹配日期值，避免文本/日期格式差异导致的匹配失败
• 锁定公休日范围$H$52:$I$57：下拉公式时不会偏移查找区域，保证逻辑一致性
• 保持优先级逻辑：先判断公休日，再区分周末/工作日

2. 实现「PH Payable hours」列的计算逻辑 #

假设：

• B列为目标日期，C列为员工当日工时，D列为修复后的「Date category」列
• 同类工作日指与公休日星期数一致的历史日期（如公休日为周一，则参考此前所有周一）

使用以下公式（Excel 365/2021直接回车，旧版本需按Ctrl+Shift+Enter触发数组计算）：

=IF(D54="PH", LET(
    target_dow, WEEKDAY(B54, 2),
    history_dates, FILTER($B$2:B53, WEEKDAY($B$2:B53, 2)=target_dow),
    history_hours, FILTER($C$2:C53, WEEKDAY($B$2:B53, 2)=target_dow),
    recent_4, TAKE(SORT(HSTACK(history_dates, history_hours), 1, -1), 4),
    valid_records, FILTER(recent_4, INDEX(recent_4,,2)>0),
    IF(ROWS(valid_records)>=3, AVERAGE(INDEX(valid_records,,2)), "")
), "")

核心逻辑拆解：

1. 仅对公休日（D54="PH"）执行后续计算
2. 提取当前公休日的星期数（target_dow，1=周一，7=周日）
3. 筛选历史所有同星期数的日期与对应工时
4. 取最近4条同星期记录，过滤掉工时为0的无效数据
5. 若有效记录≥3条，计算平均工时；否则留空

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