Overtime pay calculation

%3CLINGO-SUB%20id%3D%22lingo-sub-1615043%22%20slang%3D%22en-US%22%3EOvertime%20pay%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1615043%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3BI%20need%20help%20with%20a%20custom%20excel%20spreadsheet%20for%20timesheets.%20There%20spreadsheet%20need%20to%20calculate%20in%20a%20way%204%20-5%20different%20pays.%20What%20it%20needs%20to%20calculate%20is%20when%20a%20person%20works%20over%209%20hours%20in%20a%20single%20day%20those%20hours%20past%209%20go%20into%20overtime.%20When%20the%20total%20amount%20of%20hours%20go%20past%2070%20total%20(and%20I%20should%20note%20that%20this%20could%20happen%20while%20in%20the%20middle%20of%20a%20shift)%20the%20rest%20of%20those%20hours%20go%20into%20overtime.%20Then%20any%20additional%20hours%20worked%20past%2070%2C%20per%20day%20then%20need%20to%20have%20the%20calculation%20in%20a%20different%20column%20following%20the%20same%20order%20that%20%26nbsp%3Bup%20to%209%20in%20one%20column%20and%20anything%20past%209%20in%20the%20other%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20in%20the%20spreadsheet%20that%20I%20have%20been%20creating%2C%20there%20are%204%20columns%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1-%20Regualr%20hours%20(Under%2070%20total%2C%20less%20the%209%20per%20day)%3C%2FP%3E%3CP%3E2%20-%20Regular%20over%209%20(Under%2070%20total%2C%20beyond%209%20per%20day)%3C%2FP%3E%3CP%3E3%20-%20OT%20under%209%20(Hours%20past%2070%20total%2C%20but%20under%209%20per%20day)%3C%2FP%3E%3CP%3E3%20OT%20Past%209%20(Hours%20past%2070%20total%2C%20but%20beyond%209%20per%20day)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20a%20very%20rare%20kind%20of%20timesheet%20that%20I%20am%20having%20issue%20with%20making.%20specially%20when%20it%20comes%20to%20the%20OT.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1615043%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1615173%22%20slang%3D%22en-US%22%3ERe%3A%20Overtime%20pay%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1615173%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F306732%22%20target%3D%22_blank%22%3E%40DarStro4%3C%2FA%3E%2C%20to%20help%20us%20devise%20a%20solution%20for%20you%2C%20please%20attach%20your%20sample%20file%20with%20%3CSTRONG%3Ehard-coded%20results%3C%2FSTRONG%3E.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1615176%22%20slang%3D%22en-US%22%3ERe%3A%20Overtime%20pay%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1615176%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F306732%22%20target%3D%22_blank%22%3E%40DarStro4%3C%2FA%3E%26nbsp%3BPerhaps%20the%20attached%20workbook%20can%20guide%20you%20to%20a%20solution%20for%20your%20own%20time%20sheet.%20I%20tested%20all%20sorts%20of%20hours%20and%20it%20seems%20to%20work%20as%20you%20described%20it.%20It%20even%20works%20if%20an%20employee%20works%20less%20than%20the%20regular%209%20hours%20per%20day.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

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
Highlighted

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

Highlighted

@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.