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]

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.

 

[BigBeachBananas]

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?

It should still work when referencing a formula. I recreated the first row of your data. The sum doesn't add up to 6:57 as you showed. I'm not sure whatelse is going on with your sheet. Please do as @Peter_SSs asked and upload a minisheet using XL2BB.

Book4
	A	B	C	D	E	F	G	H	I
1								Late in hours	Deduction
2	0:58	0:21	0:35	0:29	0:23	0:28	1:02	4:16	70
Sheet1

Cell Formulas
Range	Formula
H2	H2	=SUM(A2:G2)
I2	I2	=CHOOSE(MATCH(H2*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.