Need to do a carry over formula

Question

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.&nbsp;
------------------------------Matthew Jones------------------------------

markshnier__you · Answer

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
------------------------------

miketamoush · Answer

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
------------------------------

miketamoush · Answer

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------------------------------

markshnier__you · Answer

Thx Mike!&nbsp; I have corrected my formula.&nbsp; It was originally wrong but your correction would have driven the [Over Time] to be negative if there were less than 40 hours worked.&nbsp; Anyways, I think between the two of us we have it corrected now------------------------------Mark Shnier (YQC)mark.shnier@gmail.com------------------------------

matthewjones · Answer

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
------------------------------

Forum Discussion

Need to do a carry over formula

8 Replies