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:

Start time	Finish time	Hours worked	Billable hours
Tuesday 29 August 18:00	Wednesday 30 August 07:00	13	20
Sunday 03 September 18:00	Monday 04 September 06:00	12	24

<tbody>
</tbody>

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]

Start time	Finish time	Hours worked	Billable hours
Tuesday 29 August 18:00	Wednesday 30 August 07:00	13	20
Sunday 03 September 18:00	Monday 04 September 06:00	12	24
		is start day Sunday	before 8pm	after 8am	tot hours	reg hours	premium hours	prem rate	hours
29/08/2017 18:00	30/08/2017 07:00	no	2.00	0.00	13.00	2.00	11.00	1.5	18.5
03/09/2017 18:00	04/09/2017 06:00	yes	2.00	0.00	12.00	2.00	10.00	2	22

<colgroup><col><col><col><col><col><col><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[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
.