Overtime Calculation

[ChetanPuri]

Dear Excel Team,

I am seeking some assistance in building a formula for our employees who work in shifts. Our company policy dictates that if an employee does not have an 8 hour break between the start of their shift, then the entire 7.5 hour shift should be paid at double time.

I am looking to create a formula that can calculate the time between two dates, the actual start and end time of a shift, and then populate Column U with the entire shift as OT2. For example, if an employee started their shift at 3:00 PM (Row 11, Column N) and ended at 10:30 PM (Row 11, Column P), and then had a sleepover shift for work purposes, and then worked another shift from 6:30 PM to 8:30 PM on August 8th, 2022, then those two hours would be considered overtime.

Similarly, if an employee started another shift on August 8th at 3:00 PM and the gap between their previous shift and the start of this shift was only 6.5 hours, then the entire 7.5 hour shift would be considered overtime.

I would appreciate any help in creating a formula for this.

Thank you for your assistance.

Best regards,
Chetan

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
3	Employee Code				Employment Type		Mobile Number
4	Shift Type			Shift Date	Department	Role		Area	Pay Rate	Roster Net Hours	Timesheet Net Hours	Rostered Start Time	Rostered End Time	Actual Start Time	Actual End Time	No.of HRS	Break between timeframe	Normal Hours	Sleep Over	OT1.5	OT2.0
11	Normal	Sunday		7/08/2022	Supported Living	Team Leader DS			$42.55	0.00	7.50			15:00	22:30	7.50
12	Normal	Sunday	0	7/08/2022	Supported Living	Sleepover			$42.55	0.00	0.00	22:30	06:30						8.00
13	Normal	Monday	1	8/08/2022	Supported Living	Team Leader DS			$42.55	2.00	2.00	06:30	08:30	06:30	08:30	2.00					2.00
14	Normal	Monday	0	8/08/2022	Supported Living	Team Leader DS			$42.55	7.50	7.50	15:00	22:30	15:00	22:30	7.50	6.50				7.50
15	Normal	Monday	0	8/08/2022	Supported Living	Sleepover			$42.55	0.00	0.00	22:30	06:30				0.00		8.00		0.00
16	Normal	Tuesday	1	9/08/2022	Supported Living	Team Leader DS			$42.55	2.00	2.00	06:30	08:30	06:30	08:30	2.00					2.00
17	Normal	Tuesday	0	9/08/2022	Supported Living	Team Leader DS			$42.55	7.50	7.50	15:00	22:30	15:00	22:30	7.50	6.50				7.50
18	Normal	Tuesday	0	9/08/2022	Supported Living	Sleepover			$42.55	0.00	0.00	22:30	06:30				0.00		8.00		0.00
19	Normal	Wednesday	1	10/08/2022	Supported Living	Team Leader DS			$42.55	2.00	2.75	06:30	08:30	06:30	09:15	2.75	6.50				2.75
WorkHistoryReport

Cell Formulas
Range	Formula
B11:B19	B11	=TEXT(D11,"DDDD")
P11,P19,P16:P17,P13:P14	P11	=IF(N11="","",MOD(O11-N11,1))*24
C12:C19	C12	=D12-D11
S12,S18,S15	S12	=MOD(M12-L12,1)*24
Q14,Q19,Q17	Q14	=MOD(N14-O13,1)*24
Q15,Q18	Q15	=IF(C15>=1,MOD(N15-O14,1))*24
U11:U12,U14:U15,U17:U18	U11	=IF(Q11="","",IF(Q11>=8," ",P11))
U13,U16,U19	U13	=P13

 

[Jeffrey Mahoney]

I had to copy the Rostered Date/Times over to the actual Date/Times to make this work

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
3	Employee Code				Employment Type		Mobile Number
4	Shift Type			Shift Date	Department	Role		Area	Pay Rate	Roster Net Hours	Timesheet Net Hours	Rostered Start Time	Rostered End Time	Actual Start Time	Actual End Time	No.of HRS	Break between timeframe	Normal Hours	Sleep Over	OT1.5	OT2.0
11	Normal	Sunday		8/7/2022	Supported Living	Team Leader DS			42.55	0	7.5			08/07/2022 15:00	08/07/2022 22:30	7.50					0.00
12	Normal	Sunday	0	8/7/2022	Supported Living	Sleepover			42.55	0	0	08/07/2022 22:30	08/08/2022 06:30	08/07/2022 22:30	08/08/2022 06:30				8.00		0.00
13	Normal	Monday	1	8/8/2022	Supported Living	Team Leader DS			42.55	2	2	08/08/2022 06:30	08/08/2022 08:30	08/08/2022 06:30	08/08/2022 08:30	2.00					2.00
14	Normal	Monday	0	8/8/2022	Supported Living	Team Leader DS			42.55	7.5	7.5	08/08/2022 15:00	08/08/2022 22:30	08/08/2022 15:00	08/08/2022 22:30	7.50	6.50				7.50
15	Normal	Monday	0	8/8/2022	Supported Living	Sleepover			42.55	0	0	08/08/2022 22:30	08/09/2022 06:30	08/08/2022 22:30	08/09/2022 06:30		0.00		8.00		0.00
16	Normal	Tuesday	1	8/9/2022	Supported Living	Team Leader DS			42.55	2	2	08/09/2022 06:30	08/09/2022 08:30	08/09/2022 06:30	08/09/2022 08:30	2.00					2.00
17	Normal	Tuesday	0	8/9/2022	Supported Living	Team Leader DS			42.55	7.5	7.5	08/09/2022 15:00	08/09/2022 22:30	08/09/2022 15:00	08/09/2022 22:30	7.50	6.50				7.50
18	Normal	Tuesday	0	8/9/2022	Supported Living	Sleepover			42.55	0	0	08/09/2022 22:30	08/10/2022 06:30	08/09/2022 22:30	08/10/2022 06:30		0.00		8.00		0.00
19	Normal	Wednesday	1	8/10/2022	Supported Living	Team Leader DS			42.55	2	2.75	08/10/2022 06:30	08/10/2022 08:30	08/10/2022 06:30	08/10/2022 09:15	2.75	0.00				2.75
WorkHistoryReport

Cell Formulas
Range	Formula
B11:B19	B11	=TEXT(D11,"DDDD")
P11,P19,P16:P17,P13:P14	P11	=IF(N11="","",MOD(O11-N11,1))*24
C12:C19	C12	=D12-D11
S12,S18,S15	S12	=MOD(M12-L12,1)*24
Q14,Q19,Q17	Q14	=MOD(N14-O13,1)*24
Q15,Q18	Q15	=IF(C15>=1,MOD(N15-O14,1))*24
U11:U19	U11	=IF(AND(F11<>"Sleepover",MAX($O2:O10)<>0,ROUND(N11-MAX($O2:O10),4)*24<=8),P11,0)

 

[ChetanPuri]

I had to copy the Rostered Date/Times over to the actual Date/Times to make this work

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
3	Employee Code				Employment Type		Mobile Number
4	Shift Type			Shift Date	Department	Role		Area	Pay Rate	Roster Net Hours	Timesheet Net Hours	Rostered Start Time	Rostered End Time	Actual Start Time	Actual End Time	No.of HRS	Break between timeframe	Normal Hours	Sleep Over	OT1.5	OT2.0
11	Normal	Sunday		8/7/2022	Supported Living	Team Leader DS			42.55	0	7.5			08/07/2022 15:00	08/07/2022 22:30	7.50					0.00
12	Normal	Sunday	0	8/7/2022	Supported Living	Sleepover			42.55	0	0	08/07/2022 22:30	08/08/2022 06:30	08/07/2022 22:30	08/08/2022 06:30				8.00		0.00
13	Normal	Monday	1	8/8/2022	Supported Living	Team Leader DS			42.55	2	2	08/08/2022 06:30	08/08/2022 08:30	08/08/2022 06:30	08/08/2022 08:30	2.00					2.00
14	Normal	Monday	0	8/8/2022	Supported Living	Team Leader DS			42.55	7.5	7.5	08/08/2022 15:00	08/08/2022 22:30	08/08/2022 15:00	08/08/2022 22:30	7.50	6.50				7.50
15	Normal	Monday	0	8/8/2022	Supported Living	Sleepover			42.55	0	0	08/08/2022 22:30	08/09/2022 06:30	08/08/2022 22:30	08/09/2022 06:30		0.00		8.00		0.00
16	Normal	Tuesday	1	8/9/2022	Supported Living	Team Leader DS			42.55	2	2	08/09/2022 06:30	08/09/2022 08:30	08/09/2022 06:30	08/09/2022 08:30	2.00					2.00
17	Normal	Tuesday	0	8/9/2022	Supported Living	Team Leader DS			42.55	7.5	7.5	08/09/2022 15:00	08/09/2022 22:30	08/09/2022 15:00	08/09/2022 22:30	7.50	6.50				7.50
18	Normal	Tuesday	0	8/9/2022	Supported Living	Sleepover			42.55	0	0	08/09/2022 22:30	08/10/2022 06:30	08/09/2022 22:30	08/10/2022 06:30		0.00		8.00		0.00
19	Normal	Wednesday	1	8/10/2022	Supported Living	Team Leader DS			42.55	2	2.75	08/10/2022 06:30	08/10/2022 08:30	08/10/2022 06:30	08/10/2022 09:15	2.75	0.00				2.75
WorkHistoryReport

Cell Formulas
Range	Formula
B11:B19	B11	=TEXT(D11,"DDDD")
P11,P19,P16:P17,P13:P14	P11	=IF(N11="","",MOD(O11-N11,1))*24
C12:C19	C12	=D12-D11
S12,S18,S15	S12	=MOD(M12-L12,1)*24
Q14,Q19,Q17	Q14	=MOD(N14-O13,1)*24
Q15,Q18	Q15	=IF(C15>=1,MOD(N15-O14,1))*24
U11:U19	U11	=IF(AND(F11<>"Sleepover",MAX($O2:O10)<>0,ROUND(N11-MAX($O2:O10),4)*24<=8),P11,0)

Thank you Kindly, it worked perfectly, much appreciated.