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
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
