Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
I am making a pay calculator spreadsheet.
I am trying to figure out the formula that will help me to calculate overtime. So if I have 41 hours listed then one cell will show with 40hrs and the another cell will show if over 40 the amount. So if I make 14.90hr and I have worked 42hrs. A1 shows 40hrs at 596.00 A2 shows 2hrs at 44.70(22.35hr) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
Not sure but matbe something like this
If you have 42 hours in cell A1 put this into cell B1 =IF(A1=40,40*14.9,A1*14.4) put this into cell C1 =IF(A140,(A1-40)*22.35,0) "Mandy" wrote: I am making a pay calculator spreadsheet. I am trying to figure out the formula that will help me to calculate overtime. So if I have 41 hours listed then one cell will show with 40hrs and the another cell will show if over 40 the amount. So if I make 14.90hr and I have worked 42hrs. A1 shows 40hrs at 596.00 A2 shows 2hrs at 44.70(22.35hr) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
On Nov 14, 11:16*am, Mandy wrote:
I am making a pay calculator spreadsheet. I am trying to figure out the formula that will help me to calculate overtime. So if I have 41 hours listed then one cell will show with 40hrs and the another cell will show if over 40 the amount. So if I make 14.90hr and I have worked 42hrs. A1 shows 40hrs at 596.00 A2 shows 2hrs at 44.70(22.35hr) let A1 be the hours you worked and A2 be you pay rate. assuming you get 1.5 your pay rate for over time =IF(A140,(A1-40)*A2*1.5+40*A2,A1*A2) this should do the trick |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
Little different:
A1: 42 .... Total Hours worked A2: =MAX(A1,40)*rate .... This is your gross income for regular hours B2: =MAX(A1-40,0)*rate*1.5 .... This is your gross income for OT hours -- ** John C ** "Mandy" wrote: I am making a pay calculator spreadsheet. I am trying to figure out the formula that will help me to calculate overtime. So if I have 41 hours listed then one cell will show with 40hrs and the another cell will show if over 40 the amount. So if I make 14.90hr and I have worked 42hrs. A1 shows 40hrs at 596.00 A2 shows 2hrs at 44.70(22.35hr) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
On Nov 14, 11:27*am, Paul Lambson wrote:
On Nov 14, 11:16*am, Mandy wrote: I am making a pay calculator spreadsheet. I am trying to figure out the formula that will help me to calculate overtime. So if I have 41 hours listed then one cell will show with 40hrs and the another cell will show if over 40 the amount. So if I make 14.90hr and I have worked 42hrs. A1 shows 40hrs at 596.00 A2 shows 2hrs at 44.70(22.35hr) let A1 be the hours you worked and A2 be you pay rate. assuming you get 1.5 your pay rate for over time =IF(A140,(A1-40)*A2*1.5+40*A2,A1*A2) this should do the trick <quote Not sure but matbe something like this If you have 42 hours in cell A1 put this into cell B1 =IF(A1=40,40*14.9,A1*14.4) put this into cell C1 =IF(A140,(A1-40)*22.35,0) </quote Mike has it right, but you might want to try using references instead of "hard coded" values for your wage, like what Paul did. Paul's solution is good if you want to see your total wage, but if you want to see it broken out like Mike's example, but using references for your wage instead of hard coding your wage, the formulas would look like this: Cell A1 - Hourly Wage Cell A2 - Hours worked Calculates normal pay: =IF(A240, 40* A1, A2*A1) Calculates overtime pay: =IF(A240, (A2-40)*(A1*1.5), 0) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|