Calculating Paycheck to Include Overtime

  • Thread starter Thread starter PaxtonPoe
  • Start date Start date
P

PaxtonPoe

A small portion of my worksheet is attached (29.1kb):

I DON'T USE EXCEL EVER BUT I REALLY WOULD LIKE TO KEEP A LEDGER OF M
PAY, I JUST NEED A FORMULA THAT CAN TELL ME TOTAL OF 40HR CHECK
OVERTIME (IF NEED BE) WITH TAXES(19% OF GRAND TOTAL) TAKEN OUT

I would appreciate any suggestions.
Thanks, I realize this particular question is pretty redundant aroun
here

+-------------------------------------------------------------------
|Filename: excel.JPG
|Download: http://www.excelforum.com/attachment.php?postid=4957
+-------------------------------------------------------------------
 
the above automatically includes 40hrs but some weeks I may not work 4
hours



I HAVE ATTACHED AN UPDATE OF THE WORKSHEET AND HAVE THE BACKGROUNDS O
THE FORMULAS I NEED HIGHLIGHTED IN TURQUOIS

the cells I need:
OT Hrs (D13) - I want D13 to display how many overtime hours IF ther
are more than 40 in Total Hours D12

OT Pay (D14) - Overtime pay I want to be calculated (Overtime Hours
D13 * Current Pay/hr:, A9 * Taxes Excluded, A12)

Total Pay (D16) - (Total Hrs 40 or under *Current Pay/hr:, A9 * Taxe
Excluded, A12) + OT Pay, D14

Thanks agai

+-------------------------------------------------------------------
|Filename: excel.JPG
|Download: http://www.excelforum.com/attachment.php?postid=4960
+-------------------------------------------------------------------
 
I think you need to slightly re-think your design and layout:

Calculate standard hours =MIN(40,Total hours)
Calculate Overtime hours =Max(0,D12-40)
Calculate total hours =std hours+ Ovetime hours

Calculate normal pay =std hours * hourly rate
Calculate Overtime pay =overtime hours * overtime rate #
Calculate gross pay =normal pay+overtime pay

Calculate net (after tax) pay =gross pay*(1-tax rate%)

The cells you gave are different to you Jpeg but it shoud be easy to work
out the above formulae.

# Overtime rate may be different from standard rate

HTH
 
Back
Top