Payroll: Need help with a formula that can calculate 3 shift differentials and overtime.

[DIGITALNICO]

I'm trying to build an easy to use spreadsheet for myself and some of the guys I work with.

The idea:

is that you have a Row for each day of the week. And a column for each of the different shifts(first/day, second/evenings and third/nights). All you have to do is punch in the number of hours you worked that day and the spreadsheet will calculate your time in hours and expected compensation including overtime if there are any hours over 40.

The formula I'm using:
The red text below shows the part of the formula that will calculate the hours worked in each shift (B2, C2 and D2) mutiplied by their shift differentials as long as B2+C2+D2 DOESN'T equal more than 40.

The green text below shows the part of the formula that will calculate the OVERTIME hours or anything over 40 hours worked.(G2 is the cell containing the over time rate.)

=IF(B2+C2+D2<40,(B2*F4)+(C2*F6)+(D2*F6),((B2+C2+D2-40)*G2)+??????????

The question marks show the portion of the formula I assume, (if this is the right formula to use for this application....), is missing/I don't know how to build.

The issues:

1. As you can see, B2, C2 AND D2 are different pay rates. So to get an accurate pay out amount. The formula must account for the three dif's and the overtime.

2. Currently, this formula can only calulate shift differential pay accurately under 40 hrs a week. Once the hours worked hit 40, ALL hours over 40 need to be multiplied by the overtime rate(G2) regardless of what shift they are worked one.

In semi-laymans terms:

I need the differentials to be multiplied by the hours worked like this >>> (B2*F4)+(C2*F6)+(D2*F6)
UNTIL the 40 hours a week is hit, like this >>> (B2+C2+D2>=40)
THEN anything over 40 is multiplied by the overtime rate like this >>> (40<VALUE*G2)

I've attached a picture of the 'payratecalc' so you can get an idea of the lay out and maybe a better idea of what I have in mind.

payratecalc.jpg

[Søren Larsen]

In your formula it looks to me as you are testing whether you have worked more than 40 hours on sunday. Shouldn't it be the to total of the week?

[DIGITALNICO]

The formula currently only encompasses one row. This is because it simpler for me to work with until i can figure out a formula that works the way i want. Once i get a working formula i will incorporate the rest of the cells in columns B, C and D.

[Søren Larsen]

Do you want overtime pay included in column E?

[DIGITALNICO]

yes please.

[Søren Larsen]

Well, it's not as pretty as I would have liked, but it should work. In E2 and copied down:

Please Login or Register  to view this content.