California Overtime Calculation

[Gicu]

Hi Alanna,

Have you had a chance to look at the db I posted? What is the issue with those calculations? If you could explain it I could try to help some more. I see the link you posted in your last post has a sample form to calculate the overtime, have you tried to input the data for 1355 and see what you get?

Here is what mine shows for the details:

DEPT	USR_NAM	EMP_NO	User	WEEK_NUM	TIMDATE	WEEK_TO_DATE	WEEK_TO_PREV	DATE_TOT_HRS	REG	OT
028	FELDMAN, DEBBIE	Debbie F	1355	1	2/25/2018 0:00	8.75	0	8.75	8	0.75
028	FELDMAN, DEBBIE	Debbie F	1355	1	2/26/2018 0:00	17.983	8.75	9.233	8	1.23
028	FELDMAN, DEBBIE	Debbie F	1355	1	2/28/2018 0:00	27.616	17.983	9.633	8	1.63
028	FELDMAN, DEBBIE	Debbie F	1355	1	3/2/2018 0:00	38.283	27.616	10.667	8	2.67
028	FELDMAN, DEBBIE	Debbie F	1355	1	3/3/2018 0:00	48.25	38.283	9.967	1.717	8.25
028	FELDMAN, DEBBIE	Debbie F	1355	2	3/4/2018 0:00	9.05	0	9.05	8	1.05
028	FELDMAN, DEBBIE	Debbie F	1355	2	3/5/2018 0:00	18.183	9.05	9.133	8	1.13
028	FELDMAN, DEBBIE	Debbie F	1355	2	3/6/2018 0:00	27.583	18.183	9.4	8	1.40
028	FELDMAN, DEBBIE	Debbie F	1355	2	3/7/2018 0:00	32	27.583	4.417	4.417	0.00
028	FELDMAN, DEBBIE	Debbie F	1355	2	3/9/2018 0:00	42.867	32	10.867	8	2.87
028	FELDMAN, DEBBIE	Debbie F	1355	2	3/10/2018 0:00	52.684	42.867	9.817	0	9.82

Cheers,
Vlad

[Alanna]

If someone doesn't clock out and hasn't notified payroll to have it manually entered within that pay period won't get paid for that date. It'll be made up at a later date. Unless there is a way to make the file flag it so payroll knows they have to do something. Not sure about that though.

So as you can see from the link why I keep getting confused. So I believe the image from 2:44pm pst where it shows a 80 pay period and 20.934 is the correct way to calculate. Since you cannot use the daily OT to accumulate the 40 hrs weekly.

[Alanna]

Yes I did open your link. However, for 3/3 I believe reg hours should be 8 and 1.967 for daily OT. Than on 3/10 3.583 for reg and 6.234 for OT. Your sheet looks like the closest to correct.

[Alanna]

Closest example I can find online. CA wants no OT pyramiding. On the left shows using OT to add up to the weekly 40, that's pyramiding. On the right shows a no pyramid OT and that's what I need.

[Gicu]

Hi Alanna,

Please have a look at this revised version - look at the qry_TOTALS_FOR_EXPORT_V2 query and please look at more than 1355. The individual records might look not right (see below) but the final query for export makes corrections to the totals, so as long as you only need the totals for the two week period this should work. This king of data processing is way easier (in terms of following the logic) to program in VBA but I wasn't sure if you know it.

DEPT	USR_NAM	EMP_NO	User	WEEK_NUM	TIMDATE	WEEK_TO_DATE	WEEK_TO_PREV	DATE_TOT_HRS	REG	OT
028	FELDMAN, DEBBIE	Debbie F	1355	1	2/25/2018 0:00	8.75	0	8.75	8	0.75
028	FELDMAN, DEBBIE	Debbie F	1355	1	2/26/2018 0:00	17.983	8.75	9.233	8	1.23
028	FELDMAN, DEBBIE	Debbie F	1355	1	2/28/2018 0:00	27.616	17.983	9.633	8	1.63
028	FELDMAN, DEBBIE	Debbie F	1355	1	3/2/2018 0:00	38.283	27.616	10.667	8	2.67
028	FELDMAN, DEBBIE	Debbie F	1355	1	3/3/2018 0:00	48.25	38.283	9.967	8	1.97
028	FELDMAN, DEBBIE	Debbie F	1355	2	3/4/2018 0:00	9.05	0	9.05	8	1.05
028	FELDMAN, DEBBIE	Debbie F	1355	2	3/5/2018 0:00	18.183	9.05	9.133	8	1.13
028	FELDMAN, DEBBIE	Debbie F	1355	2	3/6/2018 0:00	27.583	18.183	9.4	8	1.40
028	FELDMAN, DEBBIE	Debbie F	1355	2	3/7/2018 0:00	32	27.583	4.417	4.417	0.00
028	FELDMAN, DEBBIE	Debbie F	1355	2	3/9/2018 0:00	42.867	32	10.867	8	2.87
028	FELDMAN, DEBBIE	Debbie F	1355	2	3/10/2018 0:00	52.684	42.867	9.817	8	1.82

For export:

DEPT	USR_NAM	EMP_NO	USR_ID	OVERTIME	WEEK_OT	REGULAR	TOTAL_OT
028	FELDMAN, DEBBIE	Debbie F	1355	16.52	4.417	80.00	20.93

Cheers,
Vlad

[Alanna]

omg I think you might have it. I'm going through it now. Should I assume that 1704 is listed twice because once had a dept and the other didn't? Shouldn't be a problem going forward so...

[Gicu]

Alanna, glad to hear you got it working. Please let us know if indeed solved and please mark the thread as such.

Cheers,
Vlad

[Alanna]

Alanna, glad to hear you got it working. Please let us know if indeed solved and please mark the thread as such.

Cheers,
Vlad

My next question would be, if I plug in the next pay period are the formulas going to work?

I need to make it so that my payroll girl can easily change the payroll dates and have the results spit out so she just needs to export to csv and import into the payroll software.

[Alanna]

And I see v2 on some of these so do I assume that the duplicate names without v2 can be removed? I need to link my timecard database to this and put in the date table .... thoughts?

[Gicu]

Hi Alanna,

You could do that but I would suggest importing the queries in your database and edit them to match your own tables and fields. You would need the v2 ones (please feel free to rename them as you see fit in your db) and the other 4 that don't have a v2 equivalent.

Keep in mind that for the next pay period you will need to update the tblWeek (that is why I was asking you for your own lookup table for the dates and weeks) as mine only has the two weeks you provided data for.

Cheers,
Vlad