我正在尝试确定一起雇佣案件中的潜在责任。对于每个工作薪资期,每位员工都应获得一笔罚款。即使他们只工作了几天,他们也可能在两个不同的薪资期内工作,因此我用来计算每位员工的雇佣期内有多少个 14 天薪资期的公式不够准确。
对于每个员工,我需要确定他们应得的罚款数量,因此我需要知道他们工作了多少个工资期。
我有每个员工的雇用日期(单元格 D4)和终止日期(单元格 E4)。
工资支付周期为 14 天(第一周的周日至第二周的周六)。
罚款期的开始日期为 2014 年 11 月 9 日,即第一个工资期的第一天,日期序列号为 41952。
能否编写一个公式来计算给定时间范围内的工资期结束日期的数量?
编辑
我知道一组员工中每个员工的雇用日期和终止日期。
这些员工的工资支付期为每两周(第一周的周日至第二周的周六)。我的数据集的整个期间是 2014 年 11 月 9 日至 2019 年 9 月 7 日。总共有 126 个工资支付期,第一个工资支付期从 2014 年 11 月 9 日星期日开始)。
根据就业日期,我需要知道每位员工在其中的多少个工资期工作过。我确定每位员工就业期限内 14 天工资期数量的公式如下:
=IF((SUM(D1228-C1228)/14)<=1, "1", ROUNDUP((SUM(D1228-C1228)/14),0))
(E 列为聘用日期,F 列为终止日期)
这并没有准确地记录他们工作的工资支付期数,而只是记录了受雇和任期之间的两周数。
我的解决方案 我的解决方案并不优雅,我创建了一个查找表(“SOL!$G$2:$G$127”),为每个工资期分配一个数字(1 到 126),然后使用 VLOOKUP 获取与雇用日期和终止日期相对应的工资期的数字。然后,我从较高的数字中减去较低的数字,并加一以计算起始工资期。
获取雇用日期工资期和任期日期工资期数字的公式。起始工资期数字:
=IF(C3>=41952,LOOKUP(2,1/(SOL!$F$2:$F$127<=C3)/(SOL!$G$2:$G$127>=C3),SOL!$H$2:$H$127),1)
结束工资期数:
=IF(C3>=41952,LOOKUP(2,1/(SOL!$F$2:$F$127<=C3)/(SOL!$G$2:$G$127>=C3),SOL!$H$2:$H$127),1)
计算工资支付期总数的公式:
=IF(G3=1,0,((G3-F3)+1))
虽然不美观,但确实有效。想知道更简单的解决方案。
答案1
在 C4 中输入 2014 年 11 月 9 日(惩罚期开始日期),在 D4 中输入雇用日期,在 E4 中输入终止日期,
=ROUNDUP(SUM(E4, -MAX(C4, D4))/14, 0)
如果有空白的终止日期(例如仍在职),您可能需要截止日期。如果截止日期是,TODAY()
则更E4
改为IF(E4="", TODAY(), E4)
。
答案2
尝试:
=INT((Termination+7-WEEKDAY(Termination)-dtStart)/14)+1-INT((Hire+1-WEEKDAY(Hire)-dtStart)/14)
- dtStart:惩罚开始
- 雇用:员工雇用日期
- 终止:员工终止日期
如果终止日期可能晚于数据集的结束日期,我们可能需要做一些调整。
其工作方式与查找表类似,但不需要表格。
算法:
- 调整租用日期以反映前一个周日
- 调整终止日期以反映下一个星期六。
- 根据
startDT
,计算雇用日期的工资期。 - 根据
startDt
,计算终止支付期。 - 计算工作工资期的数量。