Forum Discussion

DarStro4's avatar
DarStro4
Copper Contributor
Aug 26, 2020

Overtime pay calculation

Hello,

   I need help with a custom excel spreadsheet for timesheets. There spreadsheet need to calculate in a way 4 -5 different pays. What it needs to calculate is when a person works over 9 hours in a single day those hours past 9 go into overtime. When the total amount of hours go past 70 total (and I should note that this could happen while in the middle of a shift) the rest of those hours go into overtime. Then any additional hours worked past 70, per day then need to have the calculation in a different column following the same order that  up to 9 in one column and anything past 9 in the other column.

 

So in the spreadsheet that I have been creating, there are 4 columns

 

1- Regualr hours (Under 70 total, less the 9 per day)

2 - Regular over 9 (Under 70 total, beyond 9 per day)

3 - OT under 9 (Hours past 70 total, but under 9 per day)

3 OT Past 9 (Hours past 70 total, but beyond 9 per day)

 

This is a very rare kind of timesheet that I am having issue with making. specially when it comes to the OT.

2 Replies

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    DarStro4 Perhaps the attached workbook can guide you to a solution for your own time sheet. I tested all sorts of hours and it seems to work as you described it. It even works if an employee works less than the regular 9 hours per day.

  • Twifoo's avatar
    Twifoo
    Silver Contributor

    DarStro4, to help us devise a solution for you, please attach your sample file with hard-coded results.

Resources