Over time Rule to Separate hours based on Pay type

Ok I have 1 more issue with creating an overtime rule in the timecard system, this morning I was able to get a 40-hour rule working to move hours from regular Earnings after 40 hours to Regular over time. But I need to make it more complicated as I have 4 Pay types employees can choose from when putting in their times for the day. Below is an example of time and pay types for days of the week for totals.

Monday – 10 Hours Regular Pay

Tuesday – 10 Hours Regular Pay

Wednesday – 10 Hours Regular Pay

Thursday – 10 Hours Training Pay

Friday – 15 Hours Training Pay

Saturday – 5 Hours Regular Pay

Total – 60 Hours

Totals For each Pay Bucket

Reg Earnings = 30 Hours

Training Pay = 10 Hours

Training Over Time = 15

Reg Over Time = 5

Right now, I have the rollover working if I just do Regular earnings using the following setup.

Field 1 - [Regular Earnings before 40 hour Cap]

If([Pay Type]="Regular Earnings", [Payroll Hours],0)

If([Pay Type]="Training Pay", [Payroll Hours],0)

If([Pay Type]="Non-Billable", [Payroll Hours],0)

Field 2 is the box for Regular Earnings before 40 hours.

[Regular Earnings]

Min(40, [Regular Earnings before 40 hour Cap])

Field 3 is the box for the Regular Over time / After 40 hours

[Over Time]

[Regular Earnings before 40 hour Cap] - [Regular Earnings]

Now I have 2 more over time Boxes – Training Pay Overtime / Non-Billable Over time. I am needing to take all the times for the week's timecard and after the 40 hours & sperate the overtime to the correct bucket and that's the issue im having. If I just use what I have not all the over time will go into the [Over Time] bucket but I need to sperate them. I was thinking a [ IF ] statement would work but I'm thinking I'm wrong as I cannot get it to do it.

------------------------------
Matthew Jones
------------------------------

App builders

Formulas and functions

11 Replies

MikeTamoush
Qrew Commander
3 years ago
How is overtime calculated? Is it possible in your example it could be 35 hours of Regular time, 5 hours of training pay, and 20 hours of training pay overtime?

I am assuming that as soon as 40 hours hits, then anything after that is overtime. Which means your formulas will be dependent on dates. What happen if you have 35 hours through Friday, then on Saturday both Training and Regular time hit for 5 hours each, which one takes the overtime hit?

I'm not sure the solution right now, but if it is date dependent I suspect it could get fairly involved.

------------------------------
Mike Tamoush
------------------------------
- MatthewJones
  Qrew Trainee
  3 years ago
  Right now it is calculated with the fields and formula i posted about using the 3 fields. But yes everything has a date on it, see screen shot below of the time card itself. Right now it is adding it all together and then when it hits 40 it rolls over to the overtime field but im lost on how to separate it into the other over time fields, but it would run like a normal time card and when 40 hours is hit it will start to move everything over time overtime bucket for that pay type.
  
  ------------------------------
  Matthew Jones
  ------------------------------
MarkShnier__You
Qrew Legend
3 years ago
Matthew, it seems to me that you would just use the exact same method as you used for regular time.

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
- MatthewJones
  Qrew Trainee
  3 years ago
  Mark,
  So i would do it for each pay type? If i did that how would it know the other pay types hours if they are not grouped? Or am i looking at it the wrong way?
  
  ------------------------------
  Matthew Jones
  ------------------------------
- MikeTamoush
  Qrew Commander
  3 years ago
  Mark,
  
  I think the issue is that All buckets can trigger the 40 hour over time kick.
  
  So if you have 20 hours Regular, 10 hours training, and 10 hours non billable, that is 40 total hours and now any bucket is triggered to overtime.
  
  But you can't just add things up, the non overtime hours go to the earliest dates/times. Every hour logged after the date/time of the entry when 40 hours was busted in the combined buckets, kicks off the over time.
  
  At least that is how I understand the posting.
  
  ------------------------------
  Mike Tamoush
  ------------------------------
  - MatthewJones
    Qrew Trainee
    3 years ago
    Mike that is correct all buckets / Pay types trigger the overtime rule. So they would all need to be grouped so when the 40 hour mark is hit it would know where to send the hours for each pay type after the 40 hour mark.
    
    So if you have 20 hours Regular, 10 hours training, and 10 hours non billable, that is 40 total hours and say I have another 5 hours of Regular time and 5 hours of training time for a total of 50 hours 5 hours of Regular time would go to regular over time bucket and 5 hours of training time would go to training over time bucket.
    
    ------------------------------
    Matthew Jones
    ------------------------------