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

 

[xgary.wilsonx]

With only one posting to your name you'll struggle to get anyone to download your file, i know i wont, to risky. Sorry

 

[greg2725d]

Thanks... at least you told me so I wasn't sitting here for hours waiting to see if somebody looks at it. haha hmmmmmmm i'll have to try to think of another way to explain I guess.

 

[Yokiboha]

I think this will do it:


=IF(SUM(C13>E5),(E5*E10)+(C13-E5)*(E10*1.5),(C13*E10))

C13 is the sum of the daily hours worked, E5 is 40 (the standard week), E10 is your hourly rate of pay.

 

[greg2725d]

That would work if I were calculating everything as a whole. What I want to do (and need to do in order to compare with what they actually pay me for) is to break it down by day (hours and $) for each category. (straight time, OT, Shift prem, etc...) They give me all this info when I get my pay check so I would like to be able to compare each day so if they pay me the wrong amount of hours I know what day they screwed up on.

it looks like this: I just threw in some numbers so it would calculate everything.

 

[Yokiboha]

Will this formula in H12 do it?

=IF(H28<40,0,SUM(J23:J27)+40-(H28))

I can see what you mean about complex company!

 

[greg2725d]

H12 is just a sum of the rows above it... what really matters is the daily calculation of it. Thats what I'll be comparing to the pay stub if something doesn't match up. . I'm a bit confused on your formula tho because row 23 27 and 28 have nothing. haha I can calculate the total for the week no problem. Thats what I originally had in there until I decided to make everything waaaaaayyy more complicated. What I really need is H7 to say .5 which will then make h12 say .5

 

[Yokiboha]

I can see your confusion...my fault

Should have been:

=IF(F10<40,0,SUM(H5:H9)+40-(F10))

However, how is I6 calculated....if 0.5? If H6 is used in the formula and H6 is 0.5, wont I6 be wrong?

 

[greg2725d]

Took me a while to figure this part out... I just realized I'm doing this quite the wrong way.... The way they calculate overtime is dumb but they give me straight time pay for everything i work... say i work 41 hours... i'd get 41 hours of straight time pay... then .5 hours of OT pay. They OT pay is calculated using my full wage. thats where the time and a half comes in. So i do need to change that too i guess.... grrr So it should look like this:

and I6 just multiply's my wage by the hours of OT i work.

 

[Yokiboha]

I am trying to get my head round this....never seen a company use such a screw eyed way of doing something that is essentially quite simple!

I this a relica of your pay slip?