Excel: How to write a formula that calculates weekly pay?

June 7, 2006 7:54 AM

Excel: How do you write a formula that calculates weekly pay?

The problem I'm trying to turn into a formula in Excel would operate something like this:
0 - 40 hours = hours * base pay
40 - 60 hours = (hours * (base pay * 1.5)) + the above calculation
60 - 80 hours = (hours * (base pay * 2)) + the above calculations

posted by dabradfo to work & money (13 comments total)

=if(hours>60,(hours-60)*basepay*2+40*basepay+20*basepay*1.5 ,if(hours>40,(hours-40)*basepay*1.5+40*basepay,hours*basepay))

Ugly, but it should do the job I think. I have assumed that if anyone works more than 80 hours, they earn double pay for the additional hours also.
posted by Touchstone at 8:06 AM on June 7, 2006

Assuming hours is in A1 and base pay's in B1:

=A1*B1+MAX(0,MIN(60,A1)-40)*B1*0.5+MAX(0,MIN(80,A1)-60)*B1
posted by kindall at 8:07 AM on June 7, 2006

Assume the hours are input into A1 and the base pay is A2


In a different cell, this would be the formula:

=IF(A1>60,((A1-60)*(A2*2))+(20*(A2*1.5))+(A1*A2),IF(A1>40,((A1-40)*(A2*1.5))+(A1*A2),A1*A2))

Enjoy.
posted by jimmy0x52 at 8:07 AM on June 7, 2006

Disregard mine - math error.

Touchstone got it right.
posted by jimmy0x52 at 8:08 AM on June 7, 2006

My formula doesn't work above 80 hours. To fix that:

=A1*B1+MAX(0,MIN(60,A1)-40)*B1*0.5+MAX(0,A1-60)*B1
posted by kindall at 8:09 AM on June 7, 2006

Further simplification of mine: =A1*B1+MAX(0,MIN(60,A1)-40)*B1/2+MAX(0,A1-60)*B1
posted by kindall at 8:11 AM on June 7, 2006

I think I might make the following columns:

A= date
B=regular hours
C= overtime 1
D= overtime 2
E total

For each row, you would enter in the info, so if you worked 75 hours, it would your b-d values would be 40, 20, 15. For this example, let's assume base pay is $5

your calculation in the total cell would be:

=(b*5)+(c*7.50)+(d*10)

I could be totally way off on this, it has been ages since I used excel for something like this.
posted by necessitas at 8:13 AM on June 7, 2006

That should be =(b1*5) + etc.
posted by necessitas at 8:14 AM on June 7, 2006

Pipped (on preview) by several good solutions; I like Kindall's - I'd never previously found a useful use for the min/max functions...

Anyway, my first reaction would have been to write a macro, for ease of flexibility and reusability:

Alt-F11 to enter the VB editor
On the little "Project - VBAProject" window in the top left, open up a folder called "Modules" - if it doesn't exist, select Insert>Module (from the menu). Double-click "Module1" to open it up in the edit window.

Paste the following code:

Function weekly_salary(hours_worked As Double, base_hourly_salary As Double) As Double

If hours_worked <= 40 Then
weekly_salary = hours_worked * base_hourly_salary
ElseIf hours_worked <= 60 Then
hours_worked = hours_worked - 40
weekly_salary = (40 * base_hourly_salary) + (hours_worked * base_hourly_salary * 1.5)
ElseIf hours_worked > 60 Then
hours_worked = hours_worked - 60
weekly_salary = (40 * base_hourly_salary) + (60 * base_hourly_salary * 1.5) + (hours_worked * base_hourly_salary * 2)
End If

End Function

If A1 holds your hourly salary, and A2 your hours worked, invoke the function by writing the following (e.g. in cell C1)

=weekly_salary(A2, A1)

And Bob is, apparently, your uncle...

Although, as per Touchstone's post, I'd pay double rate for any hours worked over 80 in a given week...
posted by Chunder at 8:14 AM on June 7, 2006

Aaaand a further simplification of mine:

=A1*B1+MAX(0,A1-40)*B1/2+MAX(0,A1-60)*B1/2

Here's how it works:

=A1*B1 = base wage

+MAX(0,A1-40) = number of hours above 40, or 0 if it's 40 or less

*B1/2 = additional half-time for hours above 40 (or nothing if 40 or less)

+MAX(0,A1-60) = number of hours above 60, or 0 if it's 60 or less

*B1/2 = additional half-time for hours above 60 (or nothing if 60 or less). since we have already given an time and a half for hours above 40, and 60 is above 40, this means that hours above 60 get double-time.
posted by kindall at 8:16 AM on June 7, 2006

uh... and just on the off chance that someone ever wants to use that macro, the 60 in the final "weekly_salary=" ... line should actually be a 20. Whoops.
posted by Chunder at 8:23 AM on June 7, 2006

Minor simplification of the Kindall Method:
=B1*(A1+MAX(0,A1/2-20,A1-50))
posted by mediaddict at 9:44 AM on June 7, 2006

A "cleaner" solution is to use Excel Array Formulas for this kind of thing, but it's an overkill for this specific problem.
posted by Sharcho at 11:24 AM on June 7, 2006