Tracking A Server's Paycheck: Regular Hours vs Overtime Hours Conflict

**azmikeed** · 03-16-2009, 04:24 AM

I want to be able to track, on a day to day basis, my regular work hours (at $10/hr), my overtime work hours (anytime over 40 hrs per week at $15/hr), and the tips I make. I have the time set up to round to the nearest quarter hour. I thought I had the rest finally figured out until I realized there are still some major calculation errors. There is a problem with the formula in column E:

=MIN(80,IF(SUM($D$3:$D$9<=40),D6*10))

E6 shows that I made $80 even though it should have also included the other .75hrs since there was no overtime that week. I only want the overtime column to have an amount more than $0.00 if in that day I worked more than 8hrs and if I also worked more than 40hrs that week. If not, I want any time more than 8hrs in a day to be calculated with that days regular hours since it does not qualify as overtime.

Any help with my formula in column E would be greatly appreciated. Also, I’m not sure if fixing that column would cause a problem with column F. Maybe column F is just “jimmy rigged” right now since column E is screwed up.

My ultimate goal is to be able to know what my next paycheck gross total will be. I’m still figuring out if this is the layout I want. Any suggestions would be great. Also, if there is a way for the Clock In and Clock Out columns (B and C) to be formatted to look like this: 5:13 PM rather than 17:13 (military time), and not affect the calculations for column D that would be awesome!

Thanks,
Mike

**zbor** · 03-16-2009, 04:58 AM

Formula is OK because you put minimum of 80 or 10*D6 wich is 8,75... So you get 80 out...

Is D9 correct or it's also wrong?

**martindwilson** · 03-16-2009, 06:01 AM

just format column b/c as anytime format you want.
add up weekly hours first then subtract 40 from total to calculate weekly ot

**azmikeed** · 03-16-2009, 01:34 PM

Originally Posted by zbor

Formula is OK because you put minimum of 80 or 10*D6 wich is 8,75... So you get 80 out...

Is D9 correct or it's also wrong?

Column D is correct. Column E is all wrong. E9 and E6 are the only cells showing incorrect results because they are the only days in that week where more than 8hrs were worked in a day. E9 should be $92.50 and E6 should be $87.50 since the total hours that week add up to less than 40hrs. I need the formula to only calculate overtime hours in column F if there are more than 40hrs worked in a single week. Otherwise, I want the hours to be calculated in the regular hours column E even if I work more than 8hrs in a single day. Week 2 looks the way I want it to only because more than 40hrs were worked that week. That's the only reason why the data looks right. However, if I worked less than 40 hours in week 2, than that week would be screwed up as well. Anytime I would work less than 40hrs in a week, the formula for column E would not produce the correct results.

I know I could just add up the total hours and subtract 40hrs from that total to get my overtime, but I want to calculate overtime on a day to day basis based on the entire weeks hours if that makes sense. Eventually I may graph my regular pay, overtime pay, and tips on a line graph.

If the way I have my rows set up is preventing the outcome I want (regular hours and overtime hours calculated per day), then let me know and I will have to settle for calculating my overtime hours at the end of the week and not have a daily income column.

Thanks,
Mike

*Attached is the file I'm working on*

**martindwilson** · 03-17-2009, 02:34 PM

how can excel possibly know what you havent put in? 9 hours on monday will be 8 plus 1 hour o/t until it knows how many you worked tuesday

**azmikeed** · 03-18-2009, 03:28 AM

If I worked 9 hrs on Monday, then I want it to calculate it as 9 regular hours until I hit 40.25 hrs or more. Once I hit overtime, I want it to reduce the payout in the regular hours column and increase the payout in the overtime column.

For example let's say I worked 9 hrs Monday, 8 hrs Tuesday, 8 hrs Wednesday, 8 hrs Thursday, and 8 hrs Friday (41 hrs total). Monday's regular hours column should show the dollar amount ($90) as if 9 regular hours were worked until Friday's 8hr shift is input, which makes the total hrs that week to exceed 40 hrs (41 to be exact - an overtime situation). Once more than 40 hrs are calculated, I want the OT column to reflect which day received the additional pay, but the regular hours column needs to drop, in this case on Monday, from $90 (9 hrs) to $80 (8 hrs). Then the OT column would go from $0 (0 hrs OT) to $15 (1 hr OT).

I know this is kind of confusing but I hope this helps. Anyway, I'm just curious if there is a way to calculate my pay per day this way with some kind of equation.

Thanks for the help,
Mike

**azmikeed** · 03-18-2009, 03:50 PM

I think I just figured it out. For column E I used this formula:
=IF(SUM($D$3+$D$4+$D$5+$D$6+$D$7+$D$8+$D$9<=40), D3*10, MIN(80,SUM(D3*10)))

When I tried it with this formula: =IF(SUM($D$3:$D$9<=40, D3*10, MIN(80,SUM(D3*10))) , I was getting nothing but problems. I thought these functions were the same but I guess not.

Anyway, attached is what I believe to be a working copy and a way to track my paychecks based on the inputs of time clocked in, time clocked out, and tips earned. It should automatically calculate, in monetary form, regular hours worked and overtime hours worked. It should also calculate, on a day to day basis and on a weekly basis, the amount of money earned based on those three inputs.

God Is Good,
Mike

**daddylonglegs** · 03-18-2009, 05:07 PM

Originally Posted by azmikeed

=IF(SUM($D$3:$D$9<=40, D3*10, MIN(80,SUM(D3*10)))

Hello Mike,

The reason this formula doesn't work is because you don't have the parentheses in the right places, you need a parenthesis to close the SUM function immediately after $D$9, I'd write it like this:

=IF(SUM(D$3:D$9)< 40,D3,MIN(8,D3))*10

Also I note that in week 2 the total hours worked are 44 but you're calculating 5¾ hours overtime, wouldn't you want that to be just 4 hours OT?

That will happen whenever you have some days where less than 8 hours are worked, e.g. extreme example.....you work 4 hours one day and 11 hours each on 4 other days, total hours worked 48 but your formulas will calculate 12 hours overtime, 3 hours each for the 4 days at 11 hours.

If that isn't what you want then it may be better just to calculate the overtime separately for the whole week.

One other observation I have is that rounding the start time to the nearest ¼ hour and the doing the same to the end time, then calculating the difference is not the same as rounding the hours worked, for example....

6:36 to 15:11 is 8:35, your formula rounds to 8.75.

6:38 to 15:13 is also 8:35, your formula rounds to 8.5

If you calculated the time difference and then rounded you wouldn't get that discrepancy.....

Note: you can format columns B and C any way you want, e.g. h:mm AM/PM,
the calculations won't be affected.

regards
daddylonglegs

**azmikeed** · 03-19-2009, 11:01 PM

Thank you daddylonglegs,

I came back to this thread because I caught the error in the overtime calculated for week 2 and saw your post. Your critique of my worksheet is very helpful.

If there is not a way to fix that error when more than 40 hrs were worked, but I work less than 8 hrs in at least one day, resulting in an incorrect calculation for the money earned in the regular and overtime hours columns, then I guess I will have to settle for just adding up the OT hours at the end of each week (exactly what I was trying to avoid doing but may be necessary after all, unless someone is up for a challenge and wants to figure it out; if it's even possible).

As far as the rounding on the time goes, where I work, both the clock in time and the clock out time is rounded separately (not the total time). So that seems to be calculating properly.

Thanks again to everyone,
Mike

P.S. I made a quick sheet that calculates money earned per week (the easy way) rather than per day (the way that creates threads much like this one) just in case there is no solution. Does anyone know how I can get rid of the Regular and Overtime columns (E and F) by adding a new equation to C24 and C25 while still getting the same results?