Overtime hours between two datetimes relative to custom weekly schedule

Will be back tomorrow for this interesting thread.
Time to get home now.

Marbleking, thank you for getting back to us.

I found something confusing.

What were we to understand the work start/work end times were for the days between work day start/work day end? Rows 18, 19 & 20 in your last upload were also 'head-scratchers'.

	A	B	C	D	E	F	G	H	I
15	Start datetime	Stop datetime	Total hours worked	Standard hours worked	Overtime hours worked		Total hours worked	Standard hours worked	Overtime hours worked
16	Tuesday 23/03/2021 05:00	Thursday 25/03/2021 06:00	49.0	32.0	17.0		49.0	32.0	17.0
17	Tuesday 23/03/2021 05:00	Thursday 25/03/2021 08:00	51.0	33.5	17.5		51.0	33.5	17.5
18	Tuesday 23/03/2021 05:00	Thursday 25/03/2021 23:00	66.0	48.0	18.0		66.0	48.0	18.0
19	Monday 22/03/2021 22:00	Thursday 25/03/2021 23:00	73.0	48.5	24.5		73.0	48.5	24.5
20	Thursday 25/03/2021 22:00	Saturday 27/03/2021 14:30	40.5	9.5	31.0		40.5	9.5	31.0
21	Friday 26/03/2021 03:00	Friday 26/03/2021 16:30	13.5	9.0	4.5		13.5	9.0	4.5
22	Friday 26/03/2021 07:00	Monday 29/03/2021 06:30	71.5	8.5	63.0		71.5	8.5	63.0
23	Monday 29/03/2021 07:00	Wednesday 31/03/2021 16:30	57.5	15.5	42.0		57.5	15.5	42.0
24	Monday 29/03/2021 07:00	Tuesday 30/03/2021 16:30	33.5	15.5	18.0		33.5	15.5	18.0

Hi Dave,

Thanks for asking. Each row with start and stop dates/times in the table is entered as an example of a continuous production process/batch that may last several operator shifts, or parts of operator shifts, but which may also need overtime if there are delays. Each row is to be seen separately from the rest of the rows, because these are only examples for calculation purposes. I have entered various times that might serve to highlight calculating difficulties. So this is not a list of serial production for one production line where each start will happen after the stop time of the former row, but rather just a way of testing the formula. Operators may e.g. normally, and most days, be working 06:30 to 14:30 and overlap with the next shift that starts 13:30 and works to 22:30. But the line would run continuously. So the "overtime" I am after is the times the line needs to run outside normal shift hours.

Regards,
Marbleking

OK and thank you.

I tried approaching the project with those assumptions and never consistently came up with the results you expected.

Intriguing and challenging problem. It kept me busy.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Not able to try with Ex365, I am using 2016, but have you tested with data like this:
Start date/En date fall into weekend/holiday?