Trying to create a time/pay chart in excel...

[Parker04GC]

The lowdown:
2 week periods
Pay schedule starts on Sunday (but actually work monday - fri and sometimes sat)
Overtime starts AFTER 40 hrs/week, not after 8 hrs/day
Minimum pay = 17.25
Overtime pay = 25.875

I've got the concept of how to add up and convert my hours. My problem is with the overtime hrs and pay. The way my spreadsheet is working right now is that it's not correct until the last day that I clock out. Up until then the pay is incorrect and sometimes in the negatives which it should never be. I need some help with formulas for adding this up correctly. Thanx

[ChemistB]

Please post an example (zipped) of your spreadsheet so we can see if we can locate the errors.

ChemistB

[Parker04GC]

I will post back in a couple days. Going on last minute trip.

[Parker04GC]

Ok, here's what I've got. This is what I can't figure out:

Adding overtime hrs (overtime accumulates only after 40 hrs/week)
Adding sick/vacation hrs

[ChemistB]

Hi Parker,

Sorry this took so long but I was away for the holidays (And working with "time" in Excel can be a real bear! ) However, I did manage to come up with formulas that do what you wanted to do. There may be simplier formulas but my brain wasn't working that way today.

1. I added a "Daily Hours" column that just calculates how many hours they worked that day (used to be your Regular Hours).
2. I always color input cells so that the user knows where they are supposed to manually input values and where they should not. You should protect the worksheet and that will then allow users to only input into the input cells (which I unlocked).
3. I put a few example equations on the right because it helped me figure out what I am doing. You can erase those.
4. I formatted my calculations as [hr]:mm and that's how the user has to enter sick or vacation time ie 8:00 for 8 hours
5. I rounded the hours to 2 decimals in the totals. Because I am approximating a 40 hour workweek to 1.66667 days when it's really 1.66666.... days. You can increase that if you want.

Let me know if you have any questions and if this is what you wanted.

ChemistB