Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Alanna is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2018
    Posts
    35

    California Overtime Calculation

    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.
    Click image for larger version. 

Name:	time data.png 
Views:	27 
Size:	30.6 KB 
ID:	33396
    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.

  2. #2
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    use the between function to get data by week, since you are doing by week. or something of this nature.

    select usr_id, sum([hrs worked by day], timecrd_dat
    from thetablename
    group by usr_id, timecrd_dat
    having ((sum([hrs worked by day]>40)) and ([timecrd_dat] between [date1] and [date2])

    hope this helps get you started

  3. #3
    Alanna is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2018
    Posts
    35
    Quote Originally Posted by Perceptus View Post
    use the between function to get data by week, since you are doing by week. or something of this nature.

    select usr_id, sum([hrs worked by day], timecrd_dat
    from thetablename
    group by usr_id, timecrd_dat
    having ((sum([hrs worked by day]>40)) and ([timecrd_dat] between [date1] and [date2])

    hope this helps get you started
    Thanks but, that is only weekly OT. I need Daily OT up to 40 hours then weekly OT.
    I have a date table that selects out the pay period. I can do a daily OT formula but I can't get it to stop if total hours equals 40 and apply the remaining hours to weekly OT.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Help us help you by providing some data we can play with rather than a picture. Either dump it into Excel and center justify it and copy/paste the range here as a table, or better yet, a zipped db with one table containing the data. I don't mind helping out, but I'm too lazy to type columns and rows of data
    You could also attach your complete db so that any code can be put right into it as long as there isn't any protected/private info in it.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Alanna is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2018
    Posts
    35
    Quote Originally Posted by Micron View Post
    Help us help you by providing some data we can play with rather than a picture. Either dump it into Excel and center justify it and copy/paste the range here as a table, or better yet, a zipped db with one table containing the data. I don't mind helping out, but I'm too lazy to type columns and rows of data:rolleyes:
    You could also attach your complete db so that any code can be put right into it as long as there isn't any protected/private info in it.

    Couldn't give you the DB even zipped too large. two weeks.zip So here is a pay period. It's in excel format. Let me know if this helps you. It has the whole of the DB but I really don't need all that

    I need the output, Dept | Usr ID| Usr_Nam | Reg Hrs |OT Hrs

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Alana,

    Would you please send us (in Excel) your week table? I take it you want to export one record per employee per week, can you please confirm?

    Cheers,
    Vlad

  7. #7
    Alanna is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2018
    Posts
    35
    Quote Originally Posted by Gicu View Post
    Hi Alana,

    Would you please send us (in Excel) your week table? I take it you want to export one record per employee per week, can you please confirm?

    Cheers,
    Vlad
    See my reply to Micron file is there. I need a one line record for a two week pay period.
    daily OT & Weekly OT per CA law.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You will have to provide some context around the data. Write down not only what you want from this, but what the results are to be based on. When it makes sense to you (and preferably someone else too) post that here. I took a quick look and stopped because
    a) does one go by the Emp_No (which isn't a number at all) and is this unique to each employee?
    b) does one overlook clock-ins if there's no corresponding clock-out?
    etc.

    Is this copy/paste from your table, or is it the data source you have to work with? Or is it the result of a query?
    I ask because it doesn't appear to be data that's normalized at all.
    Last edited by Micron; 04-04-2018 at 11:15 AM. Reason: spelin and gramur

  9. #9
    Alanna is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2018
    Posts
    35
    UsrID is unique to each person, so use this as the Key. Yes ignore any clock in that doesn't have a corresponding clock out. The excel sheet is an exact export of my database that I have to work with, the only thing I did to it was bring it down to a two week pay period.
    These are daily individual "punches" of the time clock.
    In my initial post I did a screen shot of total hours by user id by day.

    As I stated I need this output: Dept | Usr ID| Usr_Nam | Reg Hrs |OT Hrs
    One record per line per user for the two week pay period.
    California OT law can be extremely confusing and in my initial post showed an example of users. California dictates anything over 8 hours a day is OT up to 40/wk than anything over 40 is OT. Output should look like below.
    Click image for larger version. 

Name:	Untitled.jpg 
Views:	19 
Size:	44.0 KB 
ID:	33408

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Alanna,

    I was actually referring to your week/date lookup table that you use to determine what week each date belongs to (if you have one). I believe you are looking at 7 day work weeks, otherwise for a regular 5 day Mon to Fri there will be no issues (sum of the daily OTs over 8 hrs would be your total weekly OT).

    Cheers,
    Vlad

  11. #11
    Alanna is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2018
    Posts
    35
    Quote Originally Posted by Gicu View Post
    Hi Alanna,

    I was actually referring to your week/date lookup table that you use to determine what week each date belongs to (if you have one). I believe you are looking at 7 day work weeks, otherwise for a regular 5 day Mon to Fri there will be no issues (sum of the daily OTs over 8 hrs would be your total weekly OT).

    Cheers,
    Vlad
    Our workweek consists of Sun through Sat, since we are a retailer. Sum of daily OT's are not necessarily the weekly OT. If it was that simple I wouldn't need so much help.
    As you can see with user 1355 in the image of my initial post that for 1 week that user had total hours worked for the week being 52.684 however 12.684 isn't her overtime. She actually has 16.267 hours of overtime. Each days overtime up to 40 hours than all hours over 40.

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    DEPT USR_NAM EMP_NO USR_ID REGULAR OVERTIME WEEK_NUM
    028 FELDMAN, DEBBIE Debbie F 1355 33.72 14.53 1
    028 FELDMAN, DEBBIE Debbie F 1355 36.42 16.27 2

    Is this what you would expect for 1355 for the two week pay period:
    DEPT USR_NAM EMP_NO USR_ID REGULAR OVERTIME
    028 FELDMAN, DEBBIE Debbie F 1355 70.13 30.80

    Cheers,
    Vlad

  13. #13
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    If it is like I think it is please have a look at the attached file. You will see that I used some cumulative queries to get the "to date" hours for each date,

    Cheers,
    Vlad
    Attached Files Attached Files

  14. #14
    Alanna is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2018
    Posts
    35
    Ok, getting myself confused again.

    https://www.calchamber.com/californi...time-laws.aspx

    According to the above link: Weekly Overtime

    Only hours worked at straight-time apply to the weekly 40-hour limit. This prevents "pyramiding" of overtime, where an employee earns overtime on top of overtime already paid.
    Which means I'm still thinking incorrectly. So I think this is correct since you cannot accumulate OT on top of OT and only use straight time for the weekly ot.
    Click image for larger version. 

Name:	no pyramid.png 
Views:	18 
Size:	25.3 KB 
ID:	33413

  15. #15
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I take the definition to mean that if I come in on my day off for time and one-half, these hours should be excluded from any calculation. However, it is very vague at that link, and more examples would help to shed more light on the issue at that site.
    As for your data, I'm not sure where I'd begin. You have the calculations in column T so maybe no sense in worrying about any start/end times
    - unless they do or can span over midnight (as to be expected when working a night shift) and
    - these calculations have not broken them out when the shift spans 2 dates

    Seeing as how you're retail, I suspect no one ever works over midnight; not even for a Black Friday event.
    Also, are you wanting to input the start and end dates, including time, when making the calculations?

    EDIT
    from your prior answer, I guess anyone who doesn't clock out doesn't get paid for that day? You said to ignore those.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query for Overtime Staff
    By EileenAchil in forum Queries
    Replies: 1
    Last Post: 12-29-2016, 06:28 AM
  2. total time calculation for overtime
    By mathanraj76 in forum Reports
    Replies: 1
    Last Post: 06-16-2016, 10:54 AM
  3. California Wine Enthusiast - Simple Query Design Problem
    By VBNoober in forum Database Design
    Replies: 5
    Last Post: 05-21-2016, 02:00 PM
  4. Calculating overtime pay
    By mallard09 in forum Queries
    Replies: 3
    Last Post: 09-12-2014, 08:27 PM
  5. Overtime Tracking
    By cpl_usmc in forum Access
    Replies: 1
    Last Post: 06-07-2011, 03:59 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums