Differential based on percentage of hourly rate between set range hours

[DamnBrecklyn]

Please point me in the right direction. I'm trying to factor in differential pay for hours between 10pm - 6am.
Differential =10% hourly wage. Example employee makes $10 an hour so differential would equal $1 totaling $11/hr for any hours worked between 10pm - 6p

What formula designate between 10-6 the employee get paid hourly + differential

xxTimesheet1.xlsx
	A	B	C	D	E	F	G	H	I
4						date	10/10/21
5
6	Wage	$14.75
7	OT Wage	$22.13
8
9	Day	Date	Time in	Time Out	Time In	Time Out	OT In	OT Out	Total
10	Sunday	10-Oct-21	6:00 AM	11:00 AM	12:00 PM	3:00 PM	10:00 PM	12:00 AM	10.00
11	Monday	11-Oct-21	3:00 AM	8:00 AM	9:00 AM	12:00 PM	8:00 PM	10:00 PM	10.00
12	Tuesday	12-Oct-21	1:00 AM	5:00 AM	6:00 AM	10:00 AM			8.00
13	Wednesday	13-Oct-21	9:00 PM	1:00 AM	2:00 AM	6:00 AM			8.00
14	Thursday	14-Oct-21	9:00 PM	1:00 AM	2:00 AM	6:00 AM	10:00 PM	11:00 PM	9.00
15	Friday	15-Oct-21					6:00 PM	9:00 PM	3.00
16	Saturday	16-Oct-21							0.00
TimeSheet

Cell Formulas
Range	Formula
B7	B7	=B6*1.5
B10	B10	=G4
B11:B16	B11	=B10+1
I10:I16	I10	=(MOD(D10-C10,1)+MOD(F10-E10,1)+MOD(H10-G10,1))*24

 

[Soledad]

Try the formula in J2 and copy down

=24*MMULT((MOD(F10-C10,1)-MOD(E10-D10,1))*{1,0}+{-1,1}*(MMULT((MOD(H10-G10,1)>MOD(C10:D10-G10,1))*MOD(D10-G10:H10,1),{1;-1})+MMULT((MOD(H10-G10,1)>MOD(E10:F10-G10,1))*MOD(F10-G10:H10,1),{1;-1})+MOD(H10-G10,1)*((MOD(C10-G10,1)>MOD(D10-G10,1))+(MOD(E10-G10,1)>MOD(F10-G10,1)))),$B$6:$B$7)

 

[DamnBrecklyn]

Try the formula in J2 and copy down

=24*MMULT((MOD(F10-C10,1)-MOD(E10-D10,1))*{1,0}+{-1,1}*(MMULT((MOD(H10-G10,1)>MOD(C10:D10-G10,1))*MOD(D10-G10:H10,1),{1;-1})+MMULT((MOD(H10-G10,1)>MOD(E10:F10-G10,1))*MOD(F10-G10:H10,1),{1;-1})+MOD(H10-G10,1)*((MOD(C10-G10,1)>MOD(D10-G10,1))+(MOD(E10-G10,1)>MOD(F10-G10,1)))),$B$6:$B$7)

Did not work :/
Going to add additional information to clarify

 

[DamnBrecklyn]

Differential (diff) = hourly*.1 or 10% of base pay
overtime (OT) pay is applied once employee hits 40hrs
diff can be applied to OT hours however it would always be 10% of base pay rate
diff time range = 10:00 pm - 6:00 am any day of the week


EX. An employee makes $10/hr base pay. With the diff they make $11/hr total

shift 5 am - 9 am they would make $11 for the first hour then $10/hr for the rest of that shift == 11+10+10+10= $41

below is updated sheet for two employees.

xxTimesheet1.xlsx
	A	B	C	D	E	F	G	H	I
3
4						date	10/10/21
5
6	Wage	$14.75
7	OT Wage	$22.13
8
9	Day	Date	Time in	Time Out	Time In	Time Out	OT In	OT Out	Total
10	Sunday	10/10	6:00	11:00	12:00	15:00	22:00	0:00	10.00
11	Monday	10/11	3:00	8:00	9:00	12:00	20:00	22:00	10.00
12	Tuesday	10/12	1:00	5:00	6:00	10:00			8.00
13	Wednesday	10/13	21:00	1:00	2:00	6:00			8.00
14	Thursday	10/14	21:00	1:00	2:00	6:00	22:00	23:00	9.00
15	Friday	10/15					18:00	21:00	3.00
16	Saturday	10/16							0.00
17
18								Total Hours	48.00
19
20								Gross Pay	$767.00
21
22
23						date	10/10/21
24
25	Wage	$17.00
26	OT Wage	$25.50
27
28	Day	Date	Time in	Time Out	Time In	Time Out	OT In	OT Out	Total
29	Sunday	10/10	9:00 PM	11:00 PM	12:00 PM	3:00 PM	10:00 PM	12:00 AM	7.00
30	Monday	10/11	3:00 AM	8:00 AM	9:00 AM	12:00 PM	8:00 PM	10:00 PM	10.00
31	Tuesday	10/12	1:00 AM	5:00 AM	6:00 AM	10:00 AM			8.00
32	Wednesday	10/13	9:00 PM	1:00 AM	2:00 AM	6:00 AM			8.00
33	Thursday	10/14							0.00
34	Friday	10/15			3:00 AM	4:00 AM	4:00 AM	6:00 AM	3.00
35	Saturday	10/16							0.00
36
37								Total Hours	36.00
38
39								Gross Pay	$612.00
TimeSheet

Cell Formulas
Range	Formula
B7,B26	B7	=B6*1.5
B10,B29	B10	=G4
B11:B16,B30:B35	B11	=B10+1
I10:I16,I29:I35	I10	=(MOD(D10-C10,1)+MOD(F10-E10,1)+MOD(H10-G10,1))*24
I18,I37	I18	=SUM(I10:I16)
I20	I20	=Sheet1!H9
I39	I39	=Sheet1!H12

xxTimesheet1.xlsx
	A	B	C	D	E	F	G	H
4	OT Rate
5	1.5
6
7
8		Total Hours	Regular Hours	OT Hours	Rate	Regular Gpay	OT Gpay	Total Gross
9		48	40	8	14.75	590	177	767
10
11		Total Hours	Regular Hours	OT Hours	Rate	Regular Gpay	OT Gpay	Total Gross
12		36.00	36	0	17	612	0	612
Sheet1

Cell Formulas
Range	Formula
B9	B9	=TimeSheet!I18
C9,C12	C9	=IF([@Column2]>40,40,[@Column2])
D9,D12	D9	=[@Column2]-[@Column3]
E9	E9	=TimeSheet!B6
F9,F12	F9	=ROUND([@Column3]*[@Column5],2)
G9,G12	G9	=ROUND([@Column5]*$A$5*[@Column4],2)
H9,H12	H9	=SUM([@Column6]+[@Column7])
B12	B12	=TimeSheet!I37
E12	E12	=TimeSheet!B25

 

[Soledad]

Hi,
In your screenshot, for case 2 then Total hours is 36 (OT=0) but there are two days (sun & fri) the employee worked in range 10pm - 6pm, so will the employee getpaid differential for that?

 

[DamnBrecklyn]

Yes, the differential can be paid any day of the week. Overtime is any shift outside of what the employee is scheduled for regularly however they only get overtime pay once they've hit 40 hours for the working week. Working week starts Sunday and ends Saturday.

 

[Soledad]

But you havent calculated differential for case 2 in your example. Can you explain why?

 

[Soledad]

But you havent calculated differential for case 2 in your example. Can you explain why?

And differential for case 1 on Thursday

 

[DamnBrecklyn]

And differential for case 1 on Thursday

I haven't calculated for differential at all. That is what formula I need. There's no labeled cell because I couldn't figure out how even to pull the data.

 

[Soledad]

Formula for Differential is as below

=10%*B6*24*SUMPRODUCT(MMULT((MOD("6:00"-"22:00",1)>MOD(C10:D16-"22:00",1))*MOD("6:00"-C10:D16,1)+(MOD("6:00"-"22:00",1)>MOD(E10:F16-"22:00",1))*MOD("6:00"-E10:F16,1)+(MOD("6:00"-"22:00",1)>MOD(G10:H16-"22:00",1))*MOD("6:00"-G10:H16,1),{1;-1})+MOD("6:00"-"22:00",1)*((MOD(C10:C16-"22:00",1)>MOD(D10:D16-"22:00",1))+(MOD(E10:E16-"22:00",1)>MOD(F10:F16-"22:00",1))+(MOD(G10:G16-"22:00",1)>MOD(H10:H16-"22:00",1))))