Formula help needed

SOLVED

I know there's a way to do this, but nothing I've tried is working.

Trying to make a formula to calculate/post work comp expense/liability with payroll that is run each week.

In TN, 1/3 of overtime is exempt; and consider in this example that the w/c rate is .033325. 

This is the formula I've tried in many different iterations: ANSWER=-(((ADJUSTED_GROSS)-(Overtime*.3333))*.033325)

  • 0
    SUGGESTED
    The problem is that "Overtime" gives you the OT hours, not the OT wages. I don't know of any way to access the specific wage amounts or hourly rates in a formula. So the only way I know to get what you are looking for is to store their overtime rate in another field that is accessible, such as Emp_Special1_Number, so that you can multiply the hours by the rate in your formula. But you would have to make sure that the Employee Special 1 Addl Withholding field got updated any time any employee gets a raise or your formula will calculate the wrong amount. It's up to you to decide if it's worth the risk.
  • 0 in reply to StephenC
    This is the revised formula I tried, but it still isn't reducing wages by the one-third. Am I missing something?

    A=(Overtime*Emp_Special1_Number);
    B=A*.33333;
    ANSWER=-(((ADJUSTED_GROSS)-B)*.033325)
  • 0 in reply to LoriInChatt
    verified answer

    Your Adjusted_Gross is probably = 0 which is throwing off your calculation. To fix that, go to Maintain > Default Information > Employees > Company Fields. Find the line for this deduction and click the "Adjust" button. A window will open that lets you define Adjusted Gross for this deduction. Check the Gross box and save your changes. Then your formula should work.

    But let's take a step back and look at this another way. You said "In TN, 1/3 of overtime is exempt." That seems like another way of saying that the overtime premium is exempt from workers comp. Or, you need to multiply both regular hours and overtime hours times the pay rate for straight time to get the wages subject to workers comp. If that is an accurate description of what you need to do, then if you enter each employee's regular pay rate in the Employee Special 1 Addl Withholding field you could use this formula without making changes to the Adjusted Gross settings.

    A=(Regular+Overtime)*EMP_Special1_NUMBER;

    ANSWER=-A*.033325

    Of course, if your hourly pay fields are anything other than Regular and Overtime, you would have to adjust that line of the formula accordingly.

  • 0 in reply to StephenC
    Bingo! Had already confirmed the gross wages issue; I was getting an answer that still wasn't making the adjustment for exempt OT. But your 2nd solution solved the problem. I put the regular base wage in spec #1, and the 2nd shift differential wage in spec #2, and adjusted the formula for that and voila! Thanks so much!