How do I calculate salary deductions based on late attendance in minutes?

[itgldmrt]

Below is the table for late attendance deductions:

This is monthly attendance data and for example:

How do you calculate late attendance deductions with the above data based on minutes?
In the example above, an employee with the name Nara Susilowati has a salary of 10,000/month. This month, she was late, with a total of 17:11. So she is subject to a deduction >=601 = 150.

 

[itgldmrt]

Rumus perhitungan datang cepat dan telat.xls
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG		AI	AJ	AK
2	NO	NAMA	SEPTEMBER																															MENIT TELAT	TOTAL TERCEPAT
3			1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30
4			Masuk	Masuk	Masuk	Masuk	Masuk	Masuk	Masuk	Masuk	Masuk	Masuk	Masuk	Masuk	Masuk	Masuk	Masuk	Masuk	Masuk	Masuk	Masuk	Masuk	Masuk	Masuk	Masuk	Masuk	Masuk	Masuk	Masuk	Masuk	Masuk	Masuk
5	1	Agnes																																0:00	0	150
6	2	Alvita													2:51																			2:51	19	150
7	3	Anggraeni Wibowo				0:12																												0:12	19	150
8	4	Anita																																0:00	20	150
9	5	Dyah R														0:06															0:09			0:15	18	150
10	6	Fauziah Nur Sapitri						0:17									0:01												0:03					0:21	17	150
11	7	Gisella Anggasta																																0:00	20	150
12	8	Heri Setyo Budi						0:01	0:21					0:06							0:03		0:08	0:04			0:16	0:14	0:16					1:29	9	150
DATANG TELAT

Cell Formulas
Range	Formula
D5:AG12	D5	=IF(RUMUS!D5>'DATANG TELAT'!$B$1,RUMUS!D5-'DATANG TELAT'!$B$1,"")
AI5:AI12	AI5	=SUM(B5:AH5)
AJ5:AJ12	AJ5	=COUNTIF(RUMUS!D5:AH5,"<"&$B$1)
AK5:AK12	AK5	=CHOOSE(MATCH(AI5*1440,{0,31,101,301,401,501,601},1),0,50,70,90,110,130,150)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D5:AH38	Cell Value	contains "C3"	text	NO
D5:AH38	Cell Value	contains "CB"	text	NO
D5:AH38	Cell Value	contains "C2"	text	NO
D5:AH38	Cell Value	contains "C1"	text	NO
D5:AH38	Cell Value	contains "C"	text	NO
D5:AH38	Cell Value	contains "DL"	text	NO
D5:AH38	Cell Value	contains "W"	text	NO
D5:AH38	Cell Value	contains "SD"	text	NO
D5:AH38	Cell Value	contains "PG"	text	NO
D5:AH38	Cell Value	contains "A"	text	NO
D5:AH38	Cell Value	contains "I"	text	NO
AJ5:AJ38	Cell Value	>0	text	NO

Do you mean like this? @Peter_SSs & @BigBeachBananas

 

[Peter_SSs]

Do you mean like this?

Yes, exactly. Thanks.

The problem is that your SUM formula in column AI is summing from column B. It should only sum from column D.

23 10 11.xlsm
	B	C	D			G		I	J					O	P	Q	R				V	W	X	Y			AB	AC	AD	AE	AF	AG	AH	AI		AK
2	NO	NAMA	SEPTEMBER																															MENIT TELAT
3			1			4		6	7					12	13	14	15				19	20	21	22			25	26	27	28	29	30
4			Masuk			Masuk		Masuk	Masuk					Masuk	Masuk	Masuk	Masuk				Masuk	Masuk	Masuk	Masuk			Masuk	Masuk	Masuk	Masuk	Masuk	Masuk
5	1	Agnes																																0:00		0
6	2	Alvita													2:51																			2:51		70
7	3	Anggraeni Wibowo				0:12																												0:12		0
8	4	Anita																																0:00		0
9	5	Dyah R														0:06															0:09			0:15		0
10	6	Fauziah Nur Sapitri						0:17									0:01												0:03					0:21		0
11	7	Gisella Anggasta																																0:00		0
12	8	Heri Setyo Budi						0:01	0:21					0:06							0:03		0:08	0:04			0:16	0:14	0:16					1:29		50
Deduction (2)

Cell Formulas
Range	Formula
AI5:AI12	AI5	=SUM(D5:AH5)
AK5:AK12	AK5	=LOOKUP(AI5*1440,{0,31,101,301,401,501,601},{0,50,70,90,110,130,150})

 

[Peter_SSs]

Glad it seems to be sorted in the end.