MatthewJones
Qrew Trainee
3 years ago

# Need to do a carry over formula

Good morning,
I am working on a time card system and have everything working for totals but i am needing to do a formula / rule for over time carry over. So like any time card anything over 40 hours would go to over time. What i am looking to do is create a formula to move any hours from the pay type Regular Earnings over the 40 hours to the OverTime pay type totals field.

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

### 8 Replies

• np, you will copy your current complicated formula for Regular Earnings and call it [Regular Earnings before 40 hour Cap].  If you like, put it on the form to satisfy your self that it works.  Of course it will, but i know form your past posts that it's a long formula.

The you will edit you current complicated formula for Regular Earnings and replace it with just this one line.

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

That says to take the smaller of 40 and the [Regular Earnings before 40 hour Cap]

Then you will make the formula for [Over Time Total] to be

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

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
• Ok so on the Regular earnings total here is the formal I have it now. Its kind of a lot as it is pulling the data from the Payroll Hours field for each week day to separate it, this formula is used in all the totals field but changed the pay type to the type for that totals field. How would i add the formula you are talking about to this?

If([Pay Type]="Regular Earnings", [Payroll Hours],0)
+
If([Pay Type2]="Regular Earnings", [Payroll Hours2],0)
+
If([Pay Type3]="Regular Earnings", [Payroll Hours3],0)
+
If([Pay Type4]="Regular Earnings", [Payroll Hours4],0)
+
If([Pay Type5]="Regular Earnings", [Payroll Hours5],0)
+
If([Pay Type6]="Regular Earnings", [Payroll Hours6],0)

------------------------------
Matthew Jones
------------------------------
• Mathew, I think I explained the steps below.  Post back if you have a more specific question.

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
• I would change the regular earnings total field into a hidden (you can remove from form) field called 'Total Hours'.

Then your regular earnings field and overtime fields are simply formulas looking at that field.

Regular Formula says, if total hours are less than or equal 40, then show total hours, else show 40.
Over time says if total hours are under 40 show 0, otherwise show total hours minus 40.

------------------------------
Mike Tamoush
------------------------------
• I was replying at the same time as Mark. His solution I think is more elegant.

However Mark, I think you have a typo? Overtime total should be Regular Earnings before Cap - 40. Right? And probably include an if statement to only fire when cap field is over 40, so it doesnt show a negative.

------------------------------
Mike Tamoush
------------------------------
• Thx Mike!  I have corrected my formula.  It was originally wrong but your correction would have driven the [Over Time] to be negative if there were less than 40 hours worked.  Anyways, I think between the two of us we have it corrected now

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
• Ok i have 1 more thing i got it working but now need to make it complicated lol.  So i need to do the over time rule like we just did for Training time and Non-Billable time. But here is the twist they all need to count to the 40 hour rule like we just did so say i have 10 Hours regular pay on Monday Tuesday and Wednesday for a total of 30 hours Reg pay and Thursday i have 10 hours training pay and Friday i have 15 hours training pay and then Saturday 5 hours reg pay for a total of 60 hours i would need to do the same thing but i would need to calculate it all in the total area for the pay type but also do the over time in the over time totals for the pay type

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