How to create an If Formula for payroll calculations

  • 1
  • 1
  • Question
  • Updated 2 years ago
  • Answered
  • (Edited)
Originally posted in the former community durning migration:
 I need a formula to calculate the regular pay - everything under 40 hours, and then OT pay - everything over 40 hours at pay and 1/2. I have tried using If Formulas... but no luck. Any help would be greatly appreciated!! OT PAY - If([Total Work Hours] + [Total Travel (Hourly)] >40, [Total Work Hours] +[Total Travel (Hourly)]- 40*[Team Member - Hourly Rate]*1.5, [Total Work Hours] +[Total Travel (Hourly)]*[Team Member - Hourly Rate]) REG PAY - If([Total Work Hours] + [Total Travel (Hourly)] <40, [Total Work Hours] +[Total Travel (Hourly)]*[Team Member - Hourly Rate]) TOTAL WAGES - SUM([REG PAY] + [OT PAY] Any suggestions or help on what I am doing wrong??
Photo of Dan G. (QB Support)

Dan G. (QB Support), Official Rep

  • 490 Points 250 badge 2x thumb

Posted 2 years ago

  • 1
  • 1
Photo of Dan G. (QB Support)

Dan G. (QB Support), Official Rep

  • 490 Points 250 badge 2x thumb
Answer

If([Total Work Hours] + [Total Travel (Hourly)] >40, [Total Work Hours] +[Total Travel (Hourly)]- 40*[Team Member - Hourly Rate]*1.5, [Total Work Hours] +[Total Travel (Hourly)]*[Team Member - Hourly Rate]) REG PAY - If([Total Work Hours] + [Total Travel (Hourly)] <40, [Total Work Hours] +[Total Travel (Hourly)]*[Team Member - Hourly Rate]) TOTAL WAGES - SUM([REG PAY] + [OT PAY] 

Wow,

Let's try to simply this.  I like to use formula variable in my formulas to make them easier to read.  You can read about them here

http://help.quickbase.com/user-assistance/#formula_variables.html

var number TotalHours = [Total Work Hours] + [Total Travel (Hourly)];

var number $OTHours = max(0, $TotalHours - 40) // the Max ensures that this will never be less than zero.

var number RegHours = $TotalHours - $OTHours 

$RegHours * [Team Member - Hourly Rate]

+

$OTHours * [Team Member - Hourly Rate] * 1.5


Was this answer helpful?

Answer posted by:

Original
QuickBaseCoach App Dev./Training , Mark Shnier, Principal
QuickBase Solution Provider 19 days ago