Formula to calculate earnings based on multiple factors

[JRVanD]

Hello. I use this board a lot to get answers to my questions but this is my first post so please let me know if I miss anything important. I am trying to calculate some payroll figures and running into some trouble. I know this is a task that seems redundant, well it is, but we are working with multiple systems and have to reconcile everything somehow...this is what I've got to work with but I'm open to other suggestions. Our budget is very strict and each employee is allocated an exact amount for the year, any hours for our assigned roles worked over the scheduled amount needs to be coded into what we call "740". Any hours worked on special tasks are coded directly to that program; on my spreadsheet they are lumped into "Add'l Earnings" and split in the payroll software. We have employees working set schedules anywhere between 20 - 40 hours each week and I am struggling to come up with formulas that can account for scheduled hours worked, hours worked over schedule at regular time, and hours worked over schedule at overtime.

The report I use to fill in the spreadsheet shows the hours worked and I enter that number to Column O, then I want the rest to calculate based on my needs. Below is a screen shot of the spreadsheet and the formulas that I am currently using. I have been at this for too long and could really use some help so I can get this working.

For example, the employee in the second row is scheduled for 37.5 hours each week.
If they work:

1.0 - 37.5 hours:
*all straight time coded to base pay (R4)

38.0 - 40.0 hours:
*37.5 hours straight time coded to base pay (R4)
*0.5 - 2.5 hours straight time coded to regular pay over base (S4)

41.0 or more hours -
*37.5 hours straight time coded to base pay (R4)
*2.5 hours straight time coded to regular pay over base (S4) {740 Earnings (V4=S4+T4)}
*1.0 or more hours overtime coded to OT Pay (T4) {740 Earnings (V4=S4+T4)}

Here are the formulas I have come up with after much trial and error but most still aren't calculating what I need:

Columns G - J: Fixed based on employee schedule
Column O - Manual entry of actual hours worked from time clock
Column P - Regular Hours: =IF(OR(O4=40,O4>40),40,IF(O4=J4,O4,IF(AND(O4<40,O4>J4),(O4+J4)-O4,0)))
Column Q - Hours Over Schedule: =O4-P4
Column R - Base Pay: =P4*G4
Column S - Regular Pay over Base (earnings from hours worked greater than scheduled): =IF(O4=0,0, IF(J4>=40,0,IF(J4<40,(O4-J4)*G4,0)))
Column T - Overtime Pay: =IF(O4>40, (O4-40)*H4, 0)
Column U - 740 Earnings (total of all earnings over base): =S4+T4
Column V - Add'l Earnings: Flat amount based on additional work
Column W - Adjust: Flat amount based on misc. adjustments
Column X - Total Gross: =SUM(R4:T4)+SUM(V4:W4)
Column Y - Munis Total: Manual entry from payroll software
Column Z - Difference (difference between Total Gross and Munis Total - s/b zero): =Y4-X4

*I'm wondering if it might be a good idea to also add a leave column which would reduce regular earnings so overtime is based only on hours worked...

Thanks for looking!

Thanks-Ryan

 

[hernantorres23]

Hi, I'm very interested in helping you, always I have passionate in Payroll and Budgets topics, but please try prepare a data example using add-in specifically for this and it can be found here XL2BB
Pay attention to this post XL2BB 2 Squares

Thanks a lot