Ecel formula for working hours and overtime calculation

[minnal_guy2002]

Dear Expert,

I am finding some difficulties in get the data, need help to resolve this issue.

If Clock In (I2) < 8:00 AM, start counting from 8:00 AM.
If Clock In ≥ 8:00 AM, use the actual Clock In time.

If Clock Out (J2) > 8:00 PM, start counting 8:00 PM.
If Clock Out 8:00 PM, use the actual Clock Out time.

Subtract Clock Out (J2) and Break Time (2 hours).

OP&Outlet OT (Test).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	No	Emp ID	Emp Name	BRANCH	DEPT	POSITION	AREA	SECTION	Clock In	Clock Out	Breaks 2x	Total Hrs	Regular Hrs	OT Hrs
2	1	FWAC0002	ROHMOTULLAH	KKSM	SIM2-JALAN SULTAN ISMAIL 2(KL)	SHOP ASST	G1-0001	0535-SIM2	7:30	21:30	2.00	11:30	7:30	4:00
3	2	FWAC0094	MD JUNAED MIA	KKSM	SIM2-JALAN SULTAN ISMAIL 2(KL)	SHOP ASST	G1-0001	0535-SIM2	8:23	17:32	2hrs	7:09	7:09	0:00
Sheet1

Cell Formulas
Range	Formula
L2	L2	=IF(OR(I2="", J2=""), "", (J2 - MAX(I2, TIME(8,0,0))) - (2/24))
M2	M2	=IF($I$2="","",MIN(7.5/24,MIN(20/24,$J$2)-MAX(7.5/24,I2)-(2/24)))
N2	N2	=IF($I$2="","",$L$2-$M$2)
L3	L3	=IF($I$3="","",$J$3-$I$3-(2/24))
M3	M3	=IF($I$3="","",MIN(7.5/24,MIN(20/24,$J$3)-MAX(7.5/24,I3)-(2/24)))

 

[Sam_D_Ben]

May be something like this,

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	No	Emp ID	Emp Name	BRANCH	DEPT	POSITION	AREA	SECTION	Clock In	Clock Out	Breaks 2x	Total Hrs	Regular Hrs	OT Hrs
2	1	FWAC0002	ROHMOTULLAH	KKSM	SIM2-JALAN SULTAN ISMAIL 2(KL)	SHOP ASST	G1-0001	0535-SIM2	7:30	21:30	2:00	14:00	10:00	4:00
3	2	FWAC0094	MD JUNAED MIA	KKSM	SIM2-JALAN SULTAN ISMAIL 2(KL)	SHOP ASST	G1-0001	0535-SIM2	8:23	17:32	2:00	9:09	7:09	2:00
Sheet1

Cell Formulas
Range	Formula
K2:K3	K2	=TIME(2,0,0)
L2:L3	L2	=J2-I2
M2:M3	M2	=IF(OR(I2="",J2=""), "", MIN(J2, TIME(20,0,0)) - MAX(I2, TIME(8,0,0)) - K2)
N2:N3	N2	=L2-M2

 

[minnal_guy2002]

the actual clock in time is 8am and its should not count as 7.30am working time, and it has to start the calculation at 8.00am. then should minus the break time

 

[Sam_D_Ben]

Thank You