Need some Excel help. Making a finance sheet.

[Parker]

What would be a good formula for adding this up?

Adding hrs per week.
$17.25 for up to 40 hrs.
$25.875 after 40 hrs.

Let's say that:
A1 = starting time for Monday
A2 = ending time for Monday
A3 = total hrs for 1 day

A4 = start Tuesday
A5 = end Tuesday
A6 = total hrs for 1 day

A7 = start Wednesday
A8 = end Wednesday
A9 = total hrs for 1 day

A10 = start Thursday
A11 = end Thursday
A12 = total hrs for 1 day

A13 = start Friday
A14 = end Friday
A15 = total hrs for 1 day

A16 = start Saturday
A17 = end Saturday
A18 = total hrs for 1 day

A19 = total hrs for 1 week
A20 = total pay for 1 week

I will actually be making this for a 2 week period.
Currently this is the way that I'm adding up the hrs: =SUM(A3+A6+A9+A12+A15+A18)*24
This is how I'm computing the pay w/ hrs: =SUM(A19-40)*25.875+(40*17.25)

The problem that I have with this formula is that until the last day, the total pay reading is completely inaccurate and for the first couple of days reads as a negative amount. I'd like it to be precise after every day. If anyone knows of a better way, it'd be much appreciated.

 

[tel0004]

I would add up the total hours. If its less than 40, then multiply it by your rate of pay. If its over 40, multiply the hours above 40 by 28, and add the 680 or whatever you make for the first 40 hours. You would just need a few if-then statements.

 

[Parker]

I don't remember how the if/then formulas work.

Let's say that I were to get 65 hrs in a week.
So that's 40hrs @ 17.25 plus 25 hrs @ 25.875.
How would you do a if/then for that.

P.S. Overtime pay starts AFTER 40 hrs/week. Not after 8 hrs/day.

 

[tel0004]

I figured it out. At first I didn't know a simple way of making Overtime start at 40 hours, but it is easy to figure out your average pay for the week, then compute using that. If you work less than 40 hours, you make 17.25, if you work 41 hours, then that one hour at 28, would be spread over all 40 hours, giving you a average pay of 17.46. If you work 100 hours, then your average pay would be 22.43 for every hour. Once we figure out the average hourly rate, we multiply total hours by that average rate.

I then though of a second way to do it. We add up the total hours for the week. If its less than 40, your pay is 17*hours. If its greater than 40, we multiply 17*40 + (hours-40)*28.

I don't know if this makes sense or not, so I posted it, and the source code online.

This is very rough, and things are out of order, but if you get the idea, you can clean it up, and make it very usable in an hour or so.

I like the second way better. I know things are out of order, but I don't care at this stage. I like the idea of doing this, so I'll clean it up, and use it myself. My computer is on the fritz right now, the power supply is going bad. Its a very old laptop, so its not worth replacing if it does break. It may not work tomorrow morning for all I know. I'm building a new computer, so I may not get around to working on this again until Mid January. Below are pictures of the two methods. I emailed a copy of the program to myself, so I can forward it to you if you want. Just email me lilleyte at email.uc.edu with your email address. If you work on this, and clean it up abit, can you email it to me. I hope this makes sense. Its kind of tough to explain excel commands.

Method one. This was the first way I did it, but I don't like this way as much. I also realize I put time out as 68 on sunday, I was just testing to see if it worked when <40. I didn't put it in time format, but that is also easy to change.

Method 2, this one I like more, and it should be exactly what you wanted.

 

[tel0004]

Ok, I think I finally figured it all out, so I cleaned it up a bit. I did modify it for my use, so you might have to make some changes. I had trouble ]making excel add up time correctly, and keep in in number format, plus, it seemed like a lot to do, since I take unpaid lunches anyway, so to make it so less data input is required, I just changed it to total hours per day.

Sheet 1 and 2 are exactly the same, they are called Week 1, and Week 2, since I get paid biweekly. Sheet 3 computes the total pay.

I even added a spot for taxes, but I haven't filled in Federal tax yet. Since it just needs to be a rough estimate, it shouldn't be too tough. I also put in Ohio tax, which you can change to your states tax.

I also think the Pay for week column is pretty useless, so I'll get rid of that. (I need the formula's in that column, so I'll just hide it)

All I really have to do now is write protect cells, make sure my math is correct, clean up the borders, and color code the cells to make it look neater, but it is essentially done. Let me know how it works.