Payroll - Multiple Shift and Overtime Question

[mrteacher]

I have a good scenario that I am not quite sure how to proceed with. Wondering if someone has idea or thoughts on how to make this work right. It is amazing how on paper you can do some things fast -- but trying to get the formula/idea right in workbook is harder!!

employee Shift 1 Shift 2 Shift 3
John Doe 45 47 3

The employee gets over-time(time and half) for ANYTHING over 40 hrs so we would be looking at:

Shift 1: 40 Hrs reg time and 5 hrs overtime $200 + 37.50
Shift 2: 40 Hrs reg time and 7 hrs overtime $360 + 94.50
Shift 3: 3 Hrs reg time $ 36

Shift 1 pay rate: $5
Shift 2 pay rate: $9
Shift 3 pay rate: $12

Therefore the total gross pay for the John Doe is: $728

I was thinking of an IF statement but I am not sure if that is the best way to do it??

In looking at a lookup table I thought maybe this might be better too.

**The problem also runs in when the employee is working part of 2nd and part of 3rd shift when they have OVER-TIME -- for example they work 4pm to 2am here they are starting in 2nd shift but ending in 3rd shift. -- Not sure if a lookup table will do it either.

I am starting fresh on this -- there is NO prior data and all information is currently done on paper. I want to get this to an Excel Worksheet because soon I will have not 3 employees but 25 to 30 and then time calculation for 30 employees will NOT be fun!!

Thanks much
Steve

 

[SkipVought]

Hi,

you'll need a table like this, with named ranges...
[tt]
LG Shift Rate
1 1 5
1 2 9
1 3 12
2 1 7
2 2 12
2 3 15
[/tt]
Then the chart...
[tt]
emp LG 1 2 3 1 2 3
Jim 1 45 47 3 237.50 454.50 36.00
John 2 44 36 27 322.00 432.00 405.00
[/tt]
and the formula in F2
[tt]
=IF(C2>40,(C2-40)*1.5+40,C2)*SUMPRODUCT((Rate)*(Shift=F$1)*(LG=$B2))
[/tt]
using named ranges
where LG is some kind of labor grade that is a property of each employee

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[yogia]

Hi Steve:

Following is one way ...

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[LNBruno]

Or something like this:
[tt]
A B C D E F G H
1 rate 5 9 12
2
3 employee Shift 1 Shift 2 Shift 3 Pay 1st Pay 2nd Pay 3rd Total Pay
4 John Doe 45 47 3 237.50 454.50 36.00 728.00
[/tt]
With the following formulas:

E4:  =(B4*B1)+IF(B4>40,(B4-40)*B1/2,0)
F4:  =(C4*C1)+IF(C4>40,(C4-40)*C1/2,0)
G4:  =(D4*D1)+IF(D4>40,(D4-40)*D1/2,0)
H4:  =SUM(E4:G4)

< M!ke >
Your right to an opinion does not obligate me to take you seriously.
- Winston Churchill

 

[LNBruno]

sorry - misaligned the rates; they should start in column B

< M!ke >
Your right to an opinion does not obligate me to take you seriously.
- Winston Churchill

 

[mrteacher]

I really appreciate all the input -- however after I started to get into this more the "boss" decided that the "objective" was not explained clear enough to us...

Here is the what we NOW have to work with and NONE of this is coming from the a computer input system -- like a scan card on the mfr floor. It is ALL hand keyed data for each employee.

A B C D E F G H
Emp S1 S2 S3 ttl M Shift Tbl Hrs (Mil. Time)
J.D. 8 2 2 12 1 1 05:00 - 17:00
8 2 2 12 1 2 17:00 - 05:00
8 2 2 12 1
0 4 8 12 2
0 4 8 12 2

If we enter in col "F" the number 1 or 2 then the vlookup with put in the right numbers in "B"-"D" -- that part is easy.

Where the problem runs into is that once 40 hrs is reached then everything after that is over-time. Since in this example there are a ttl of 60 hrs - 20 of them are over-time. BUT the real issue is that over time is different per shift because each shift has different pay rates.

So the user needs to know at what POINT in a shift the over-time is also paid at 1.5 times. (Example - it might be half-way through 2nd or 3rd or 1st shift -- depending on when they work that day.)

Part of this is very EASY for me to come up with - the other part is much more over my head -- although it has been many yrs since I have been really absorbed into Excel.

Many thanks for the prior input on this...
Steve

 

[yogia]

Hi Steve:

I suggest you study the solutions that have been posted ... if you can follow those solutions you should be able to formulate a solution that will meet your changed requirements.

It is better to base the solution on understanding the basics, because believe me you have not heard the last of what the boss has to say about changing the requirements.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[PeterMoran]

Hi,

I would suggest that, as you have twice mentioned overtime calculations, you must prepare some data examples identifying the different overtime situations which may occur and discuss those with your boss - it is imperative that this be agreed by all concerned before any work is started.

The other components appear to have been covered above but I would encourage the use of the KISS principle, keeping it simple!!

Good Luck!

Peter Moran