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.

 

[BigBeachBananas]

Try this. Adjust cell reference as needed.

=CHOOSE(MATCH(A1*1440,{0,31,101,301,401,501,601},1),0,50,70,90,110,130,150)

 

[Peter_SSs]

Sorry, it works if the time is not a formula. However, the total late attendance using the “SUM” formula is why it doesn't work.
What if the formula could be used with the "SUM" formula?

Please refer to post #6

The sum doesn't add up to 6:57 as you showed.

I'm assuming that the SUM formula includes all the hidden columns between columns C & P since row 3 looks like dates.

 

[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]

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

An easy way would be to re-design the lookup table like below (though the middle column is not actually required).

23 10 11.xlsm
	B	C	D	E	F	G	H
1						Late	Deduction
2	0	30	0			17:11	150
3	31	100	50			0:23	0
4	101	300	70			2:05	70
5	301	400	90			9:00	130
6	401	500	110
7	501	600	130
8	601		150
Deduction

Cell Formulas
Range	Formula
H2:H5	H2	=VLOOKUP(G2*1440,B$2:D$8,3)

 

[Peter_SSs]

Try this. Adjust cell reference as needed.

=CHOOSE(MATCH(A1*1440,{0,31,101,301,401,501,601},1),0,50,70,90,110,130,150)

If hard-coding the values into the formula then you could do it with a single function like this.

23 10 11.xlsm
	G				K
1	Late				Deduction
2	17:11				150
3	0:23				0
4	2:05				70
5	9:00				130
Deduction

Cell Formulas
Range	Formula
K2:K5	K2	=LOOKUP(G2*1440,{0,31,101,301,401,501,601},{0,50,70,90,110,130,150})

In my mind though the lookup table has the advantage that if the range limits or deduction amounts change, that only needs to be done in one place rather that alter a formula and copy it to all the relevant cells.

 

[itgldmrt]

Try this. Adjust cell reference as needed.

=CHOOSE(MATCH(A1*1440,{0,31,101,301,401,501,601},1),0,50,70,90,110,130,150)

It doesn't work.

 

[Peter_SSs]

Can you give us that sample data with XL2BB as mentioned above? Then we ccould be sure to be testing with the same data/values/formulas that you are.

 

[itgldmrt]

Try this. Adjust cell reference as needed.

=CHOOSE(MATCH(A1*1440,{0,31,101,301,401,501,601},1),0,50,70,90,110,130,150)

Sorry, it works if the time is not a formula. However, the total late attendance using the “SUM” formula is why it doesn't work.
What if the formula could be used with the "SUM" formula?

 

[itgldmrt]

Can you give us that sample data with XL2BB as mentioned above? Then we ccould be sure to be testing with the same data/values/formulas that you are.

Your formula also works but is hampered by the total "SUM" formula that I mentioned above.