Creating a Timesheet with at different rates

Question

Scenario:

I have an excel Timesheet which needs to calculate Standard Time 1x, Overtime 1.5x, Overtime 2x based on the following values:

1. If the start time and finish time fall between 08:00 and 16:45, then sum the hours x1
2. If the finish time falls between 16:45 and 00:00, then sum the hours by x1.5
3. If the finish time falls between 00:00 and 08:00 then sum hours by x2

Example Data:

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

I have been racking my brain and possible ways to do this but keep falling short, does anybody know how I would go about creating a formula to do this kind of some?

Answer 1

Ah, I am too late as the other answer has been accepted which is great if it covers a 48-hour period. Anyway this is how mine looks - can provide some explanation later.

Here are the definitions relative to cell D2

So the basic formula is

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

This is based on the standard formula for calculating the overlap of two ranges quoted in various places e.g. here but adds 1 (equal to one whole day) for the case when the end of shift is after midnight. This works for standard time (starting in D2) and overtime X 1.5 (starting in E2) as long as the end of the evening shift (in J4) is also entered as 1 whole day (24:00 hours). For overtime X 2, both the start of shift (0:00) and end of shift (08:00) have to be adjusted by 24 hours if the hours worked are split across midnight so the formula in F2 is

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