Breaking Up A Range Of Time

[Ark68]

I am struggling to find an efficient means of taking an employee's shift and breaking it down into different time components.

Consider this shift: 2:00PM Saturday - 2:00AM Sunday.
The rules for overtime are:
Hours between 7:00AM and 3:00PM Saturdays are at time an one half (x1.5).
Hours between 3:00PM Saturday and 0:00AM Monday are at double time (x2)

I am trying to work out code that will helf me find the appropriate values for x1.5 (OT1) and x2 (OT2) allocations respectively. I run into all kinds of crazy results when I work with time especially when it comes with crossing into the next dat

'so far .....
sostm = .cells(tgt_r,9) 'start of shift time
eostm = .Cells(tgt_r, 10) 'end of shift time
sosdt = DateValue(.Cells(2, 5) & " " & .Cells(2, 6) & ", " & .Cells(2, 4)) 'start of shift date
sos = sosdt + sostm 'start date-time 
eos = '?????????? 'end date-time
elghrs = .Cells(tgt_r, 11) 'hours of shift
othrs = elghrs
ot1 = '??????? 'hours between 7am and 3pm Saturday
ot2 = '??????? 'hours between 3pm Saturday and 0:00A Monday (all day Sunday)
MsgBox "Start of Shift: " & Format(sos, "ddd dd-mm hh:mm A/P" & Chr(13) & _
       "End of Shift: " & Format(eos, "ddd dd-mm hh:mm A/P" & Chr(13) & _
       "Eligible for: " & othrs & " hrs. overtime.")

Example:
sostm = 2:00:00 PM
eostm = 2:00:00 AM
sosdt = 43974
sos = 43974.583333
eos = ?
elghrs = 12
othrs = 12

The expected results:
ot1 = 1 (2:00P - 3:00P Saturday)
ot2 = 11 (3:00P Sat - 2:00A Sunday)

Thank you.

0.583333​

0.083333​

 

[Sulprobil]

Hi sbTimeDiff. Thank you for sharing that! Again, a very thorough solution, but perhaps a bit too complicated for me to deal with. Despite that though, it has provied some additional education I didn't have 30 minutes ago, so it's appreciated.

Looking for possibles solutions to my issues encountered in post #4, I've raised that unique problem with a cross posted question here.

Kirk's sample calculation is totally ok, I think.
In my humble opinion the UDF sbTimeDiff is simplifying things by encapsulating the desired functionality:

Breaking_up_a_range_of_timelsm.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Weekday	Start	End	Saturday1	Start	End	Saturday2	Start	End	Saturday3	Start	End	Sunday	Start	End
2	Monday	0:00	24:00	Monday			Monday			Monday			Monday
3	Tuesday	0:00	24:00	Tuesday			Tuesday			Tuesday			Tuesday
4	Wednesday	0:00	24:00	Wednesday			Wednesday			Wednesday			Wednesday
5	Thursday	0:00	24:00	Thursday			Thursday			Thursday			Thursday
6	Friday	0:00	24:00	Friday			Friday			Friday			Friday
7	Saturday			Saturday	0:00	7:00	Saturday	7:00	15:00	Saturday	15:00	24:00	Saturday
8	Sunday			Sunday			Sunday			Sunday			Sunday	0:00	24:00
9	Holidays			Holidays			Holidays			Holidays			Holidays
10
11				Rate factor	1	1	1,5	2	2
12
13	Scenario	In	Out		Hrs between Limits	Hrs between Limits	Hrs between Limits	Hrs between Limits	Hrs between Limits	Regular Pay Rate ($/h)	Pay with overtime ($)
14	1	21.09.2019 06:00	22.09.2019 01:00		0:00	1:00	8:00	9:00	1:00	20	660,00
15	2	07.09.2019 17:54	08.09.2019 03:32		0:00	0:00	0:00	6:06	3:32	20	385,33
16	3	22.05.2020 22:00	23.05.2020 14:00		2:00	7:00	7:00	0:00	0:00	20	390,00
17	4	23.05.2020 21:30	24.05.2020 16:15		0:00	0:00	0:00	2:30	16:15	20	750,00
18	5	24.05.2020 18:00	25.05.2020 08:30		8:30	0:00	0:00	0:00	6:00	20	410,00
19	6	24.05.2020 18:00	26.05.2020 08:30		32:30	0:00	0:00	0:00	6:00	20	890,00
Sheet1

Cell Formulas
Range	Formula
E14:E19	E14	=sbTimeDiff($B14,$C14,Weekday)
F14:F19	F14	=sbTimeDiff($B14,$C14,Saturday1)
G14:G19	G14	=sbTimeDiff($B14,$C14,Saturday2)
H14:H19	H14	=sbTimeDiff($B14,$C14,Saturday3)
I14:I19	I14	=sbTimeDiff($B14,$C14,Sunday)
K14:K19	K14	=J14*SUMPRODUCT(E14:I14,$E$11:$I$11)*24

Named Ranges
Name	Refers To	Cells
Saturday1	=Sheet1!$E$2:$F$9	F14:F19
Saturday2	=Sheet1!$H$2:$I$9	G14:G19
Saturday3	=Sheet1!$K$2:$L$9	H14:H19
Sunday	=Sheet1!$N$2:$O$9	I14:I19
Weekday	=Sheet1!$B$2:$C$9	E14:E19

Maybe you find out when you need to take holidays into account