基于雇佣与离职日期的Excel受薪员工月度工时计算自动化需求问询

需求背景 #

基于全年2080工时标准，需根据受薪员工的雇佣日期、离职日期（若有）自动计算月度工作时长。此前需手动筛选全月在职员工计标准工时，对当月入职/离职员工单独用NETWORKDAYS函数计算，现需实现全场景自动化，并支持扩展至全年各月度计算。

需覆盖的计算场景（以2023年1月为例） #

• 雇佣日期≤2023年1月1日，且无离职日期（或离职日期晚于1月31日）：计全月标准工时（如2023年1月为176工时）
• 雇佣日期≤2023年1月1日，且离职日期在1月内：按1月1日至离职日期用NETWORKDAYS计算工时（乘以8）
• 雇佣日期早于2023年1月1日但离职日期早于该日：计0工时
• 雇佣日期在2023年1月内且晚于1日：按入职日期至1月31日用NETWORKDAYS计算工时（乘以8）
• 雇佣日期晚于2023年1月：计0工时

通用自动化公式 #

使用以下公式可自动适配所有场景，且支持扩展至全年各月度：

=MAX(0, NETWORKDAYS(MAX(月度起始日, 雇佣日期), MIN(EOMONTH(月度起始日, 0), IF(离职日期="", DATE(9999,12,31), 离职日期)))*8)

公式说明 #

1. 月度起始日：指计算月份的第一天（如2023年1月填2023/1/1）
2. 雇佣日期：员工的入职日期单元格（如D2）
3. 离职日期：员工的离职日期单元格（如E2，若为空则视为永久在职）
4. 逻辑拆解：
   • 取月度起始日与雇佣日期的较晚值作为实际工作起始日
   • 取月度月末（EOMONTH(月度起始日,0)）与离职日期（无则用远未来日期）的较早值作为实际工作结束日
   • 若实际起始日晚于结束日，返回0；否则用NETWORKDAYS计算工作日数并乘以8得到工时

全年扩展方案 #

1. 在表格顶部创建一行，依次填入全年12个月的第一天（如2023/1/1、2023/2/1...2023/12/1）
2. 为第一行员工的第一个月输入上述公式，将月度起始日指向对应月份的单元格
3. 横向拖动公式至全年12列，纵向拖动至所有员工行，即可自动生成所有员工的全年月度工时

示例验证 #

• Test员工2023年2月工时（当月离职）：公式自动匹配为NETWORKDAYS("2/1/2023",E3)*8
• Test 2员工2023年2月工时（当月入职）：公式自动匹配为NETWORKDAYS(D4,EOMONTH("2/1/2023",0))*8
• Test 3员工2023年1月工时（全月在职）：公式自动匹配为NETWORKDAYS("1/1/2023",EOMONTH("1/1/2023",0))*8

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