excel - 创建不同费率的时间表

Question

场景:

我有一个 Excel 时间表，需要根据以下值计算标准时间 1x、加类时间 1.5x、加类时间 2x:

1. 如果开始时间和结束时间介于 08:00 和 16:45 之间，则将小时数相加 x1
2. 如果结束时间介于 16:45 和 00:00 之间，则将小时数加 x1.5
3. 如果结束时间介于 00:00 和 08:00 之间，则将小时数加 x2

示例数据:

Start time  Finish time Standard time   Overtime x1.5   Overtime x2
08:30:00     17:00:00       7.5             0.5              0
17:00:00     01:00:00       0               7                1
01:00:00     10:00:00       2               0                7
06:00:00     12:00:00       4               0                2

我一直在绞尽脑汁寻找可能的方法来做到这一点，但始终未能达到目的，有人知道我将如何创建一个公式来做到这一点吗？

Answer 1

啊，我太晚了，因为其他答案已被接受，如果它涵盖 48 小时的时间段，那就太好了。无论如何，这就是我的样子 - 稍后可以提供一些解释。

以下是与单元格 D2 相关的定义

所以基本公式是

=MAX(0,MIN(ClockOff+Split,ShiftEnd)-MAX(ClockOn,ShiftStart))

这是基于计算不同地方引用的两个范围重叠的标准公式，例如here但如果轮类结束时间是在午夜之后，则加 1(等于一整天)。这适用于标准时间(从 D2 开始)和加类 X 1.5(从 E2 开始)，只要夜类结束时间(在 J4 中)也输入为 1 全天(24:00 小时)。对于加类 X 2，如果工作时间分布在午夜，则类次开始时间 (0:00) 和类次结束时间 (08:00) 都必须调整 24 小时，因此 F2 中的公式为

=MAX(0,MIN(ClockOff+Split,ShiftEnd+Split)-MAX(ClockOn,ShiftStart+Split))