We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Calculate wage in excel?

Medium Priority
364 Views
Last Modified: 2012-05-11
I'm trying to calculate a wage for an employee that works X amount of hours.. but of course I have to contend with Overtime, which makes the calculation difficult.  I need to calculate the Wage times the first 40 hours worked, and then multiply the wage by 1.5 for the additional hours worked in the week.

Is there an easy way to do that in excel? wage-demo.xlsx
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2008
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT
Most Valuable Expert 2013
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
=IF(C2>40,SUM(40*B2,(C2-40)*(B2*1.5)))
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT
Top Expert 2008
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Thanks for the QUICK help!
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
=B2*(MIN(40,C2)+MAX(0,C2-40)*1.5)
CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
OK, how about

=(C2*3-MIN(C2,40))*B2/2

barry
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
=I
where I is a defined name containing your formula. :)
CERTIFIED EXPERT
Top Expert 2008

Commented:
Rory, that's cheating. Harry Barry whatever his name is wins. I have too much real work to do.
CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
No functions?

=(C2>{0,40})*(C2-{0,40})*{1,0.5}*B2

barry
CERTIFIED EXPERT
Top Expert 2008

Commented:
Now you are just showing off ;-)

Author

Commented:
If I wanted this formula to just calculate regular wages if the employee worked less than 40 hours, how would I do it?  Right now if the employee has worked 0 hours, it still calculates them as 40.
CERTIFIED EXPERT
Top Expert 2008

Commented:
Any of the above formulas will handle less than 40 hours.

Kevin
i know mine will work for less than 40...............i didnt check the others........
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.