Calculate overtime

[Microsoft]

Hello,

I occasionally work set shifts, which includes an overtime rate. I would like to create a table which looks like the following:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Start time[/TD]
[TD]Finish time[/TD]
[TD]Hours worked[/TD]
[TD]Billable hours[/TD]
[/TR]
[TR]
[TD]Tuesday 29 August 18:00[/TD]
[TD]Wednesday 30 August 07:00[/TD]
[TD]13[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]Sunday 03 September 18:00[/TD]
[TD]Monday 04 September 06:00[/TD]
[TD]12[/TD]
[TD]24[/TD]
[/TR]
</tbody>[/TABLE]

I am a contractor, and my hourly multiplier works to the following rules:

- Before 20:00 (weekday) single time.
- After 20:00 and before 08:00 the following day (weekday) 1.5x hours.
- Any Sunday shift (regardless of end date) 2x hours.

Using the first line as an example - I worked 2 hours at single time (18:00 - 20:00) then a subsequent 12 hours at 1.5x hours, so that equates to 2+18 = 20.

I will then create a 5th column which will be my hourly rate x the billable hours.

I'd be really grateful for any help!

 

[etaf]

on your sunday example

18:00 - midnight = sunday = 6 x 2
midnight - 06:00 = Monday before 08:00 weekday = 6 x 1.5

however you see that all the hours as sunday 18:00 sunday to 06:00 monday , which is 12 hours and you have x2 for sunday rate for them all

so I assume sunday is from saturday 20:00 to Monday at 08:00
is that correct

Also you consider Saturday a weekday ?
is that correct

 

[oldbrewer]

[TABLE="width: 1027"]
<colgroup><col><col><col><col><col><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Start time[/TD]
[TD]Finish time[/TD]
[TD]Hours worked[/TD]
[TD]Billable hours[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tuesday 29 August 18:00[/TD]
[TD]Wednesday 30 August 07:00[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sunday 03 September 18:00[/TD]
[TD]Monday 04 September 06:00[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]24[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]is start day Sunday[/TD]
[TD]before 8pm[/TD]
[TD]after 8am[/TD]
[TD]tot hours[/TD]
[TD]reg hours[/TD]
[TD]premium hours[/TD]
[TD]prem rate[/TD]
[TD]hours[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]29/08/2017 18:00[/TD]
[TD="align: right"]30/08/2017 07:00[/TD]
[TD]no[/TD]
[TD="align: right"]2.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]13.00[/TD]
[TD="align: right"]2.00[/TD]
[TD="align: right"]11.00[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]18.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03/09/2017 18:00[/TD]
[TD="align: right"]04/09/2017 06:00[/TD]
[TD]yes[/TD]
[TD="align: right"]2.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]12.00[/TD]
[TD="align: right"]2.00[/TD]
[TD="align: right"]10.00[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]22[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Microsoft]

Sorry, I should have explained this further.

As long as my start time is on a Sunday, the charge is 2x.

I will never be asked to work on a Saturday, but if I was, the rate would be 1.5x from Friday 20:00 until Saturday 00:00.

 

[etaf]

as asked
so I assume sunday is from saturday 20:00 to Monday at 08:00
is that correct
BUT as you say if you did work on a Saturday then it would be 1.5x upto midnight on Saturday
What about Sunday midnight until monday 8am - whats the rate

Just need to make sure we know exactly when the rates kick in and out for all hours of the week

- Monday to Friday between 08:00 and 20:00 1.0x hours.
- Monday to Friday Between 20:00 and 08:00 1.5x hours.
- Friday 20:00 to Saturday Midnight is 1.5x
- Sunday 00:00 until ??????? is this Monday 08:00 is 2x hours
.