比加班费计算

Question

我正在编写一个简单的时间表，它应该计算overtime1和overtime2，我只是想不出一个很好的方法来修复它。

正常工作时间为8小时，从07:00到17:00不加班(如07:00至16:00或08:00至17:00)，但当时间超过8小时时，我想在牢房里加班。

我的业务规则是：

1)任何在06:00至20:00之间工作时间超过8小时的工作，均以overtime1支付。

2)任何少于8小时的工作即使在20:00之后也不会产生任何overtime2。

3)任何早于06:00或20:00之前完成的工作，均按overtime2费率支付。

例1:从07:00到6:00工作将得到3小时的overtime1值

示例2:从14:00到22:00工作将产生0小时的overtime2。

示例3:工作05:00到21:00将给overtime1 6小时和overtime2 2小时(在06:00前1小时，20:00后1小时)。

Answer 1

如果您的数据布局巧妙，这是一个很容易解决的问题。以A列为时钟，B列为时钟结束时间，使用此公式作为辅助，以确定是否应在06:00时使用时钟或基准：

=IF(A1-FLOOR(A1,2)<6/24,6/24,A1-FLOOR(A1,2))

然后使用此公式确定是否应该使用超时时间或20:00：

=IF(B1-FLOOR(B1,2)>20/24,20/24,B1-FLOOR(B1,2))

然后减去两者，得到一天的分数，乘以24，转换为小时，然后减去8，得到overtime1小时。组合成一个超级公式，在C1中如下所示：

OT1: =IF(OR(ISBLANK(A1),ISBLANK(B1)),"",(IF(B1-FLOOR(B1,2)>20/24,20/24,B1-FLOOR(B1,2))-IF(A1-FLOOR(A1,2)<6/24,6/24,A1-FLOOR(A1,2)))*24-8)

请记住，Excel格式的日期为1= 24小时。此外，我还添加了一个OR(ISBLANK(A1),ISBLANK(B1))语句，以确保在其中一个值为空的情况下获得一个空字符串。

从overtime2开始，您需要将其分成两部分: 06:00之前和20:00之后。第一部分检查时钟是否早于06:00，如果是的话，计算出多少个小时。这个公式最终的结果是：

=IF(IF(A1-FLOOR(A1,2)<6/24,6/24,A1-FLOOR(A1,2))<=6/24,(6/24-(A1-FLOOR(A1,2)))*24,0)

对于20:00以后，使用相同的模式。找出一天中有多少时间在20点以后被记录下来。最后的公式是：

=IF(IF(B1-FLOOR(B1,2)>20/24,20/24,B1-FLOOR(B1,2))>=20/24,((B1-FLOOR(B1,2))-20/24)*24,0)

最后，要计算加班费2的总数，只需在D1中将这两个公式相加：

OT2: =IF(OR(ISBLANK(A1),ISBLANK(B1)),"",IF(IF(A1-FLOOR(A1,2)<6/24,6/24,A1-FLOOR(A1,2))<=6/24,(6/24-(A1-FLOOR(A1,2)))*24,0)+IF(IF(B1-FLOOR(B1,2)>20/24,20/24,B1-FLOOR(B1,2))>=20/24,((B1-FLOOR(B1,2))-20/24)*24,0))

Answer 2

这只是为了使逻辑正确和理解，例如，Excel将07:00视为与7/24相等的十进制。

OT1

=IF(NOT(AND(Sheet1!$A2>=7/24,Sheet1!$B2<=17/24)),MIN(20/24,Sheet1!$B2)-Sheet1!$A2-9/24,0)

OT2

=IF(MIN(20/24,Sheet1!$B2)-Sheet1!$A2-9/24,MAX(B2-20/24,0),0)

​

​