Calculate wage in excel?

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
LVL 1
Kyle WitterAsked:
Who is Participating?
 
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
Use this formula:

=MIN(40,C2)*B2+MAX(0,C2-MIN(40,C2))*B2*1.5

Kevin
0
 
barry houdiniConnect With a Mentor Commented:
Try this formula to cope with any number of hours

=MIN(40,C2)*B2+MAX(0,C2-40)*B2*1.5

regards, barry
0
 
KnutsonBMCommented:
=IF(C2>40,SUM(40*B2,(C2-40)*(B2*1.5)))
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
KnutsonBMConnect With a Mentor Commented:
there was an error in my last one:

=IF(C2>40,SUM(40*B2,(C2-40)*(B2*1.5)),C2*B2)
0
 
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
Short formula contest?

=C2*B2+MAX(0,C2-40)*B2*0.5

Kevin
0
 
Kyle WitterAuthor Commented:
Thanks for the QUICK help!
0
 
Rory ArchibaldCommented:
=B2*(MIN(40,C2)+MAX(0,C2-40)*1.5)
0
 
barry houdiniCommented:
OK, how about

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

barry
0
 
Rory ArchibaldCommented:
=I
where I is a defined name containing your formula. :)
0
 
zorvek (Kevin Jones)ConsultantCommented:
Rory, that's cheating. Harry Barry whatever his name is wins. I have too much real work to do.
0
 
barry houdiniCommented:
No functions?

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

barry
0
 
zorvek (Kevin Jones)ConsultantCommented:
Now you are just showing off ;-)
0
 
Kyle WitterAuthor 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.
0
 
zorvek (Kevin Jones)ConsultantCommented:
Any of the above formulas will handle less than 40 hours.

Kevin
0
 
KnutsonBMCommented:
i know mine will work for less than 40...............i didnt check the others........
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.