Rate Calculation on basis of working hours

scorpene

New Member
Joined
Aug 2, 2019
Messages
5
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 ?
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

sfpowell

Board Regular
Joined
Mar 30, 2009
Messages
79
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

New Member
Joined
Aug 2, 2019
Messages
5
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:
Total Hours MayTotal Hours JuneTotal Hours July
Employee A41:1486:1933:26
Employee B51:1426:0697:40
Employee C90:59104:2313:36

<tbody>
</tbody>

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

Thanks !!
 

scorpene

New Member
Joined
Aug 2, 2019
Messages
5
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

Board Regular
Joined
Mar 30, 2009
Messages
79
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'
Hours - Low EndBaseHourly Rate
0820050
20.01920065
40.011050080
60.011210095
85.0114475110

<tbody>
</tbody>

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

New Member
Joined
Aug 2, 2019
Messages
5
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

New Member
Joined
Aug 2, 2019
Messages
5
Sorry. I was using "False" instead of "True" in Vlookup. Got it correct. Thanks

God Bless You
 

Watch MrExcel Video

Forum statistics

Threads
1,099,122
Messages
5,466,814
Members
406,500
Latest member
Tknotmaxx

This Week's Hot Topics

Top