Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Payroll Overtime/Doubletime calculator

Topic Labels: Formulas
Solved
Jump to Solution
423 2
cancel
Showing results for 
Search instead for 
Did you mean: 

Building a payroll system and need formula help with splitting up total hours between Daily Hrs, time and a half pay, and doubletime. Here’s my setup:

Screen Shot 2020-10-06 at 8.36.09 AM

My “Total Hrs/day” is the actual total of all hours worked, calculated with:

(OUT-IN)-BREAK

Then, I want to limit the amount of hours from the “Total Hrs/Day” that can go into “Daily Hrs” to 8 hours of regular pay. I do this with:

IF((OUT-IN)-BREAK<=8, (OUT-IN)-BREAK, 😎

This means that if 10 hours were worked, “Daily Hrs” is capped at 8.

I want the remaining two hours to populate into the “time and a half” to be multiplied by that 1.5x rate. The caveat is that I need this to be capped at 4 hours of overtime, before spilling any remaining hours from the “Total Hrs/day” into “doubletime”.

IF(
(OUT-IN)-BREAK>8,
((OUT-IN)-BREAK)-8,
BLANK()
)

I can then spill the remaining hours into the “time and a half” with this formula above, but I can’t figure out how to also cap it at 4 hours before sending the remainder to doubletime. I tried this but am having some issues.

IF(
AND(
(OUT-IN)-BREAK ≤ 8,
(OUT-IN)-BREAK ≥ 12,
((OUT-IN)-BREAK) -8)
)
4
)

Thanks in advance for your help!

1 Solution

Accepted Solutions

You can simplify your formulas a lot using MIN() to cap a field value at either 8 or 4, and then simple subtraction to get the hours for doubletime:

  • {Daily Hrs} =
    • IF({Total Hrs/day}, MIN({Total Hrs/day}, 8), BLANK())
  • {Time and A Half Hrs} =
    • IF({Total Hrs/day}, MIN({Total Hrs/day} - {Daily Hrs}, 4), BLANK())
  • {Doubletime hrs} =
    • IF({Total Hrs/day}, {Total Hrs/day} - {Daily Hrs} - {Time and A Half Hrs}, BLANK())

See Solution in Thread

2 Replies 2

You can simplify your formulas a lot using MIN() to cap a field value at either 8 or 4, and then simple subtraction to get the hours for doubletime:

  • {Daily Hrs} =
    • IF({Total Hrs/day}, MIN({Total Hrs/day}, 8), BLANK())
  • {Time and A Half Hrs} =
    • IF({Total Hrs/day}, MIN({Total Hrs/day} - {Daily Hrs}, 4), BLANK())
  • {Doubletime hrs} =
    • IF({Total Hrs/day}, {Total Hrs/day} - {Daily Hrs} - {Time and A Half Hrs}, BLANK())

Thank you so much Kamille, this is a super elegant solution!

Cheers