跨月份场景下薪资计算的日期周数确定及公式优化求助

规则明确 #

自定义薪资月规则：以自然月的第一个周一作为当月首日，每月固定4周（28天）；若周期跨到下一个自然月，区间内所有日期仍归为起始周一所在的自然月。
例：2023年2月第一个周一是2月6日，因此自定义2月的范围为2023/02/06 ~ 2023/03/05，此区间内日期均属于自定义2月，周数从1到4。

一、计算目标日期所属的自定义月份 #

公式（A6为目标日期单元格）： #

=SE(A6 >= DATA(ANO(A6), MÊS(A6), 1) + MOD(8 - DIA.DA.SEMANA(DATA(ANO(A6), MÊS(A6), 1), 2), 7), MÊS(A6), MÊS(DATA(ANO(DATA(ANO(A6), MÊS(A6), 1)-1), MÊS(DATA(ANO(A6), MÊS(A6), 1)-1), 1) + MOD(8 - DIA.DA.SEMANA(DATA(ANO(DATA(ANO(A6), MÊS(A6), 1)-1), MÊS(DATA(ANO(A6), MÊS(A6), 1)-1), 1), 2), 7)))

公式说明： #

1. DATA(ANO(A6), MÊS(A6), 1) + MOD(8 - DIA.DA.SEMANA(..., 2), 7)：计算目标日期所在自然月的第一个周一
2. 逻辑判断：
   • 若目标日期≥当前自然月第一个周一，返回当前自然月的月份数
   • 否则，返回上一个自然月第一个周一所在的月份数

二、计算目标日期所属的自定义周数 #

公式（A6为目标日期单元格）： #

=INT((A6 - SE(A6 >= DATA(ANO(A6), MÊS(A6), 1) + MOD(8 - DIA.DA.SEMANA(DATA(ANO(A6), MÊS(A6), 1), 2), 7), DATA(ANO(A6), MÊS(A6), 1) + MOD(8 - DIA.DA.SEMANA(DATA(ANO(A6), MÊS(A6), 1), 2), 7), DATA(ANO(DATA(ANO(A6), MÊS(A6), 1)-1), MÊS(DATA(ANO(A6), MÊS(A6), 1)-1), 1) + MOD(8 - DIA.DA.SEMANA(DATA(ANO(DATA(ANO(A6), MÊS(A6), 1)-1), MÊS(DATA(ANO(A6), MÊS(A6), 1)-1), 1), 2), 7)))/7) + 1

公式说明： #

1. 通过嵌套的SE函数定位目标日期所属自定义月的起始周一
2. 计算目标日期与起始周一的天数差，除以7取整数后加1，得到周数（差值0-6天为第1周，7-13天为第2周，以此类推）

示例验证 #

• 输入日期2023/02/15：
  • 自定义月份公式返回2（2月）
  • 周数公式返回2，符合预期
• 输入日期2023/03/05：
  • 自定义月份公式返回2（2月）
  • 周数公式返回4，符合预期

可选优化：拆分辅助单元格 #

若觉得嵌套公式过长，可拆分辅助单元格简化操作：

1. 辅助单元格B6（当前自然月第一个周一）：

   =DATA(ANO(A6), MÊS(A6), 1) + MOD(8 - DIA.DA.SEMANA(DATA(ANO(A6), MÊS(A6), 1), 2), 7)
   

2. 辅助单元格C6（上一个自然月第一个周一）：

   =DATA(ANO(B6-1), MÊS(B6-1), 1) + MOD(8 - DIA.DA.SEMANA(DATA(ANO(B6-1), MÊS(B6-1), 1), 2), 7)
   

3. 简化后的自定义月份公式：

   =SE(A6 >= B6, MÊS(B6), MÊS(C6))
   

4. 简化后的周数公式：

   =INT((A6 - SE(A6 >= B6, B6, C6))/7) + 1
   

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