Calculating overtime

[greg2725d]

OK... I've been staring at this for a few weeks now and I just cant figure it out. I work for a very complex company that change rules, schedules, pay etc. all the time. Because they do this, they have a horrible reputation for not getting our paychecks correct. So I wanted to create a spreadsheet so I can keep track of my own pay but make it able to adapt to all of the different circumstances that this company may throw at me. I'm pretty sure I got almost everything down except 1 thing. I'm having a really hard time with the overtime calculation. Overtime is really tricky there.

What happens is this: We are guaranteed (right now) a 10 hour day. (we are on 4 - 10 hour shifts). So if we work 9 hours say on Monday, we get 9 hours of straight time and an hour of short work week (approx 80% of pay). Now if we work 11 hours on Tuesday (which they can do unfortunately) I get 11 hours of straight time and no overtime. We have to make up for the short work week hour. So a less complicated explanation would be if I dont work more than 40 hours per week, no overtime no matter what I actually worked per day. Seems pretty simple but what I want and need to do is to calculate it per day.

So back to the example. If i work this:

Mon 10 Hours
Tues 11 Hours
Wed 9.5 Hours
Thurs 10 hours

I should get:

Mon 0 overtime
Tues .5 overtime
Wed 0 overtime
Thurs 0 overtime

Thats where I'm having my issue. I got it to the point where If the day where we get short work week is first and overtime after that, it works. But if we work overtime first then get short work week later in the week, it wont calculate it. I know why it wont work now but I dont know how to make it work. LOL Here's a link to the file. there's some pretty complex formulas in there so any questions feel free to ask. I'd really appreciate some input on this. I've been trying to figure out this problem for weeks.

http://dl.dropbox.com/u/1683289/2010.xlsx

 

[greg2725d]

What I'll be using this for is to compare what the computers tell me i'm getting paid for at work. Their computers will show me how many straight time hours, OT hours, Shift premium hours, short work week hours, holiday hours and vacation hours i get paid for each day. the $ thing is there just for fun i guess. (might not have been worth it if you see the calculation for shift premium, don't know if you've actually download the file or not but its a bit ridiculous)

 

[xgary.wilsonx]

Since you have provided data i have had a look and think i have got it, well i'm pretty **** sure i have, how do i upload a file to show you or whats your email and i'll send you what i've done.

Gary

 

[Yokiboha]

Hi Gary

Send the file to jpozzi@mirific.co.uk

So the 0.5 entry in H6 is the overtime premium, not the hours worked?

 

[greg2725d]

that is correct... that is how much i will get paid overtime by... It should technically be .25 because they just multiply that by my full wage not half. so they cut my OT hours in half and multiply it by my full wage instead of doing it like the rest of the world does it and multiply how many hours i worked as overtime by half my wage. lol

 

[Yokiboha]

Sorry Gary, thought that was from Greg

 

[xgary.wilsonx]

Greg

I just sent a file to Yokiboha that i think is what you want, do you want a copy?

If so i can send it to you on e-mail

Gary

 

[greg2725d]

yes please. greg2725d@gmail.com Thank you!

 

[xgary.wilsonx]

its on its way

Gary

 

[xgary.wilsonx]

There are additional columns but these can be hidden once its built.

 

[greg2725d]

close... however if i work 3 - 11 hour shifts and a 9.5 hour shift for a total of 42.5 hours... i only get 1.5 hours of OT. it works for our specific example, but not everything.