HI I need some help. I'm not a guru at access, I'm definitely a beginner. I'm a fast learner and have done several reports and queries. However, I'm now stumped.
Above is a snippet of time card data I've imported from another database, normally this will be for 2 weeks, I just have one pulled right now. It came in by individual punches and I have it totaling by day.
California overtime law states, anything over 8 hours per day is OT and anything that is over 40 per week is OT. But you can't pyramid or as I say double dip.
Examples from above image: user 1257 has two days where over 8 hours is OT but the total hours for the week are under 40.
I need an output that says 1257 - 36.683 reg hours 1.584 OT hrs (again it'll be for two weeks).
User 1355 output should read 1355 40 reg hours OT 16.267 hrs - this person receives a daily OT up to 40 hours than a weekly OT. dates 3/4 to 3/7 = 32 hours. 8 hours of 3/9 is reg hours and the balance is OT and 3/10 is all OT.
So basically,
3/4 8 reg 1.05 OT
3/5 8 reg 1.133 OT
3/6 8 reg 1.4 OT
3/7 4.417 reg
3/9 8 reg 2.867 OT
3/10 9.817 OT
I need one line per user for a result to export to a csv to upload into the payroll program.
This calculation is so far over my head it's orbiting earth. And I don't think I can get the single line output in excel either (of course that would be a killer formula too.)
I have no idea how to use Visual Basics in access, but willing to learn.