具体加班计算

我正在制作一个简单的时间表，应该计算加班时间1和加班时间2，我只是想不出一个好的方法来解决它。

正常工作日 8 小时从 07:00 到 17:00 不加班（例如 07:00 到 16:00 或 08:00 到 17:00），但是当时间超过 8 小时时，我想将超出的时间记录在细胞。

我的业务规则是：

1) 06:00 至 20:00 之间任何超过 8 小时的工作均按加班工资1。

2) 任何少于8小时的工作不会产生任何加班2，即使是在20:00之后

3) 任何早于 06:00 或晚于 20:00 完成的工作均按加班费 2 支付工资。

示例 1：从 07:00 工作到 18:00 将获得 3 小时加班费1

示例 2：从 14:00 工作到 22:00 将产生 0 小时加班2。

示例 3：工作时间 05:00 至 21:00 将给予加班 1 6 小时和加班 2 2 小时（06:00 之前 1 小时，20:00 之后 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 得到加班小时数 1。结合到一个超级公式中，它在 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))语句以确保在其中一个值为空时获得空字符串。

从加时赛2开始，您需要将其分为两部分：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:00 之后记录了多少部分。最终的公式为：

=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))