Rate Calculation on basis of working hours

[scorpene]

Hello,

I have an excel that contains total month wise time for each employee in columns. Total amount for each employee needs to be as calculated based on a slab. The slab is

For 0-20 Hours: $8200 + $50 Per Hour
For 21-40 Hours: $9200 + $60 Per Hour

Now if a person has worked 38:55 Hours in a month then how the amount can be calculated as per this slab ?

 

[sfpowell]

Try this formula:

=IF(A1*24<=20, 8200+50*A1*24, IF(A1*24<=40, 9200+60*A1*24, "40+"))

Where cell A1 is the cell is where you have the total hours worked in Excel time format. I think the biggest trick is that Excel treats any time format as a decimal number where one day equals 1. So, if the hours are in time format then you would have to multiply it by the 24 hours in a day to convert it to hours in decimal format. Then it’s a simple IF formula.

Also, you didn’t mention what would happen if working hours is greater than 40. If the formula is different you can stick it in place of the “40+” that I have above. If it’s really just less than 20 or more than 20 then you can simplify the formula to:

=IF(A1*24<=20, 8200+50*A1*24, 9200+60*A1*24)

 

[scorpene]

Thank You for your reply.

I would like to reiterate the entire scenario again with the slab:

For 0-20 Hours : $8200+50 Per Hour
For 21-40 Hours : $9200+65 Per Hour
For 41-60 Hours : $10500+80 Per Hour
For 61-85 Hours : $12100+95 Per Hour
For 86 and above : $14475+110 Per Hour

Sample data for your reference:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Total Hours May[/TD]
[TD]Total Hours June[/TD]
[TD]Total Hours July[/TD]
[/TR]
[TR]
[TD]Employee A[/TD]
[TD]41:14[/TD]
[TD]86:19[/TD]
[TD]33:26[/TD]
[/TR]
[TR]
[TD]Employee B[/TD]
[TD]51:14[/TD]
[TD]26:06[/TD]
[TD]97:40[/TD]
[/TR]
[TR]
[TD]Employee C[/TD]
[TD]90:59[/TD]
[TD]104:23[/TD]
[TD]13:36[/TD]
[/TR]
</tbody>[/TABLE]

Can you please tell me how to calculate on the basis of this table and slab

Thanks !!

 

[scorpene]

Thank You so much man. You are a life saver. Thank you so much once again.

i was able to create this formula as an extension from your formula :

=IF(ISBLANK(C3)," ",IF(C3*24<=20,8200+50*C3*24,IF(C3*24<=40,9200+60*C3*24,IF(C3*24<=60,10500+80*C3*24,IF(C3*24<=85,12100+95*C3*24,IF(C3*24>85,14475+110*C3*24," "))))))


If you can suggest any thing better or any other edit, i'd be more than happy more learn it.

 

[sfpowell]

That formula certainly works. You can get a long ways in Excel by just nesting formulas like that. One big negative of doing it that way is that it becomes much more difficult to change if your rate table ever changes. If you want to make it fancier and a bit more robust you could create a table with your rate values and then reference that table for the formula.

So, if you create a table like below and name it 'RateTable'
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Hours - Low End[/TD]
[TD]Base[/TD]
[TD]Hourly Rate[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]8200[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]20.01[/TD]
[TD]9200[/TD]
[TD]65[/TD]
[/TR]
[TR]
[TD]40.01[/TD]
[TD]10500[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]60.01[/TD]
[TD]12100[/TD]
[TD]95[/TD]
[/TR]
[TR]
[TD]85.01[/TD]
[TD]14475[/TD]
[TD]110[/TD]
[/TR]
</tbody>[/TABLE]

Then you could use the VLOOKUP formula to shorten the equation to this:

=VLOOKUP(A2*24,RateTable,2,TRUE) + VLOOKUP(A2*24,RateTable,3,TRUE)*A2*24

...where A2 is the total hours in Excel time format.

Setting the spreadsheet up this way means that if you ever have to change your rates, you simply have to update the table and all the formulas will update based on that.

 

[scorpene]

Hello sfpowell,

Thanks once again for your inputs. I was trying this formula (=VLOOKUP(A2*24,RateTable,2,TRUE) + VLOOKUP(A2*24,RateTable,3,TRUE)*A2*24) in the same manner which you just posted but the result shows #N/A. I am using the exact formula with correct cell reference but still this formula is giving me error.

Can you please help me on this.

 

[scorpene]

Sorry. I was using "False" instead of "True" in Vlookup. Got it correct. Thanks

God Bless You