Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Cell Formula Help
I have 5 employees.
How would you write this formula to calculate time and 1/2 over 40 hrs. in a single week if even over 40hrs for the week and then calcuate the same for the second week then figure the single day also. B5 to H5 is 1 week, I5 to O5 is 2nd week, P5 is a single day. S5 = total hours. T5 is Gross Pay = total hours x 8 an hr. Myrna -- Thank you |
#2
|
|||
|
|||
Hi Myrna!
It depends on how you pay overtime and what you mean by "then figure the single day also". Do you pay OT only after an employee has worked 40 hrs? Or, do you pay OT after an employee has worked 8 hrs a day? What if an employee works 12 hrs on Monday and then calls off on Tuesday? You say that P5 is a single day. I have no idea what that means. Your basic setup should include a cell to calculate regular hours and then another cell should calculate OT hours. Biff -----Original Message----- I have 5 employees. How would you write this formula to calculate time and 1/2 over 40 hrs. in a single week if even over 40hrs for the week and then calcuate the same for the second week then figure the single day also. B5 to H5 is 1 week, I5 to O5 is 2nd week, P5 is a single day. S5 = total hours. T5 is Gross Pay = total hours x 8 an hr. Myrna -- Thank you . |
#3
|
|||
|
|||
something like assuming a1 = hourly rate=8/hr t5=sum(b5:h5)*a1+if(sum(b5:h5)40,sum(b5:h5)-40,0)*a1*1.5+ sum(i5:o5)*a1+if(sum(i5:o5)40,sum(i5:o5)-40,0)*a1*1.5+ p5*a1 B5 to H5 is 1 week, I5 to O5 is 2nd week, P5 is a single day. S5 = total hours. T5 is Gross Pay = total hours x 8 an hr -- duane ------------------------------------------------------------------------ duane's Profile: http://www.excelforum.com/member.php...o&userid=11624 View this thread: http://www.excelforum.com/showthread...hreadid=276115 |
#4
|
|||
|
|||
Biff,
My table includes a row from B5 through P5. This is 15 days or cells. I pay overtime within a calendar week of Mon through Sun only (7 days). If they exceed 40 hours within 7 cells of the 15 cells. How do I calculate the formula for the whole 15 arrangement. "Biff" wrote: Hi Myrna! It depends on how you pay overtime and what you mean by "then figure the single day also". Do you pay OT only after an employee has worked 40 hrs? Or, do you pay OT after an employee has worked 8 hrs a day? What if an employee works 12 hrs on Monday and then calls off on Tuesday? You say that P5 is a single day. I have no idea what that means. Your basic setup should include a cell to calculate regular hours and then another cell should calculate OT hours. Biff -----Original Message----- I have 5 employees. How would you write this formula to calculate time and 1/2 over 40 hrs. in a single week if even over 40hrs for the week and then calcuate the same for the second week then figure the single day also. B5 to H5 is 1 week, I5 to O5 is 2nd week, P5 is a single day. S5 = total hours. T5 is Gross Pay = total hours x 8 an hr. Myrna -- Thank you . |
#5
|
|||
|
|||
Duane,
I inserted this formula T5 cell. Made A1 = 8 So I inserted 6 into B5 all the way to P5. The formula is giving me a different figure than I should have. Myrna "duane" wrote: something like assuming a1 = hourly rate=8/hr t5=sum(b5:h5)*a1+if(sum(b5:h5)40,sum(b5:h5)-40,0)*a1*1.5+ sum(i5:o5)*a1+if(sum(i5:o5)40,sum(i5:o5)-40,0)*a1*1.5+ p5*a1 B5 to H5 is 1 week, I5 to O5 is 2nd week, P5 is a single day. S5 = total hours. T5 is Gross Pay = total hours x 8 an hr -- duane ------------------------------------------------------------------------ duane's Profile: http://www.excelforum.com/member.php...o&userid=11624 View this thread: http://www.excelforum.com/showthread...hreadid=276115 |
#6
|
|||
|
|||
Hi!
Try this: =IF(S580,(S5-80)*A1*1.5+80*A1,S5*A1) Biff -----Original Message----- Duane, I inserted this formula T5 cell. Made A1 = 8 So I inserted 6 into B5 all the way to P5. The formula is giving me a different figure than I should have. Myrna "duane" wrote: something like assuming a1 = hourly rate=8/hr t5=sum(b5:h5)*a1+if(sum(b5:h5)40,sum(b5:h5)-40,0) *a1*1.5+ sum(i5:o5)*a1+if(sum(i5:o5)40,sum(i5:o5)-40,0)*a1*1.5+ p5*a1 B5 to H5 is 1 week, I5 to O5 is 2nd week, P5 is a single day. S5 = total hours. T5 is Gross Pay = total hours x 8 an hr -- duane -------------------------------------------------------- ---------------- duane's Profile: http://www.excelforum.com/member.php? action=getinfo&userid=11624 View this thread: http://www.excelforum.com/showthread...hreadid=276115 . |
#7
|
|||
|
|||
Biff,
This worked great but my delima is. B5 to H5 = 7 days I5 to O5 = 7 days S5 = 1 day T5 is hourly rate $8 If my worker works 49 hours the first 7 days. He will be paid overtime for this. If my worker works 15 hours the 2nd 7 days. There is no overtime And the final day 1 hour. There is no overtime. Notice the total hours add up to 65 hours. I made a mistake on how to reference the delima in my last post. Need to calculate the formula in a different way. "Biff" wrote: Hi! Try this: =IF(S580,(S5-80)*A1*1.5+80*A1,S5*A1) Biff -----Original Message----- Duane, I inserted this formula T5 cell. Made A1 = 8 So I inserted 6 into B5 all the way to P5. The formula is giving me a different figure than I should have. Myrna "duane" wrote: something like assuming a1 = hourly rate=8/hr t5=sum(b5:h5)*a1+if(sum(b5:h5)40,sum(b5:h5)-40,0) *a1*1.5+ sum(i5:o5)*a1+if(sum(i5:o5)40,sum(i5:o5)-40,0)*a1*1.5+ p5*a1 B5 to H5 is 1 week, I5 to O5 is 2nd week, P5 is a single day. S5 = total hours. T5 is Gross Pay = total hours x 8 an hr -- duane -------------------------------------------------------- ---------------- duane's Profile: http://www.excelforum.com/member.php? action=getinfo&userid=11624 View this thread: http://www.excelforum.com/showthread...hreadid=276115 . |
#8
|
|||
|
|||
Hi Myrna!
I've got to be perfectly honest with you and say that this method makes absolutely no sense to me.(and I've been doing this sort of thing for years). The formulas and the math needed are easy but I'm having a hard time with the logic. I think your "delima" would be lessened considerably if you used intermediate cells between weeks to calculate the individual weekly totals. How does this 1 day cell figure into the calculation of OT? If your employee works 40 hrs in both wk1 and wk2 and 8 hrs on this 1 extra day, is that 1 extra day OT? If your employee works 44 in wk1 and 48 hrs in wk2 and 6 hrs on this 1 extra day, how is that paid? I don't understand this 1 day cell! Need a more detailed explanation. Biff -----Original Message----- Biff, This worked great but my delima is. B5 to H5 = 7 days I5 to O5 = 7 days S5 = 1 day T5 is hourly rate $8 If my worker works 49 hours the first 7 days. He will be paid overtime for this. If my worker works 15 hours the 2nd 7 days. There is no overtime And the final day 1 hour. There is no overtime. Notice the total hours add up to 65 hours. I made a mistake on how to reference the delima in my last post. Need to calculate the formula in a different way. "Biff" wrote: Hi! Try this: =IF(S580,(S5-80)*A1*1.5+80*A1,S5*A1) Biff -----Original Message----- Duane, I inserted this formula T5 cell. Made A1 = 8 So I inserted 6 into B5 all the way to P5. The formula is giving me a different figure than I should have. Myrna "duane" wrote: something like assuming a1 = hourly rate=8/hr t5=sum(b5:h5)*a1+if(sum(b5:h5)40,sum(b5:h5)-40,0) *a1*1.5+ sum(i5:o5)*a1+if(sum(i5:o5)40,sum(i5:o5)-40,0) *a1*1.5+ p5*a1 B5 to H5 is 1 week, I5 to O5 is 2nd week, P5 is a single day. S5 = total hours. T5 is Gross Pay = total hours x 8 an hr -- duane ----------------------------------------------------- --- ---------------- duane's Profile: http://www.excelforum.com/member.php? action=getinfo&userid=11624 View this thread: http://www.excelforum.com/showthread...hreadid=276115 . . |
#9
|
|||
|
|||
Biff,
That is exactly what I did. Created a seperate cell to add up the hours for the first 7 days in Nov. and a second cell for the next 7 days. This is a semi-monthly time sheet. So the next sheet covers the dates between 16-31st. I have designed 2 spreadsheets for a Semi-monthly time schedule 1st = 11/1 to 11/15 are the dates 2nd = 11/16 to 11/31 are the dates Payperiod is Mon - Sun. November end on a Tuesday. December starts on a Wednesday. The trouble is I have a formula for the cell of the 1st sheet ranging from B6:H6 which November turn out to luckily start out on a Mon = B6 and Sunday = H6 The formula will not work when the next Month appears on my new spreadsheet. How would I rewrite the formula. =IF(B2340,(B23-40)*C6*1.5+40*C6,B23*C6) C6= hourly rate I created a seperate spreadsheet called Payroll So, I pasted and copied the total hrs from the 1st time schedule in a cell on my Payroll sheet. This is why you see B23, which at the bottom of this sheet. Was this done right. The formula work great on figuring overtime from Mon - Sun. But will not work for Dec, Jan because the days end up at different location on a calendar. This is my spreadsheet for the 1st semi=mthly time schedule: A B C D E F G H Person 1 Mon Tue Wed Thur Fri Sat Sun etc 2 11/1 11/2 11/3 etc. etc "Biff" wrote: Hi Myrna! I've got to be perfectly honest with you and say that this method makes absolutely no sense to me.(and I've been doing this sort of thing for years). The formulas and the math needed are easy but I'm having a hard time with the logic. I think your "delima" would be lessened considerably if you used intermediate cells between weeks to calculate the individual weekly totals. How does this 1 day cell figure into the calculation of OT? If your employee works 40 hrs in both wk1 and wk2 and 8 hrs on this 1 extra day, is that 1 extra day OT? If your employee works 44 in wk1 and 48 hrs in wk2 and 6 hrs on this 1 extra day, how is that paid? I don't understand this 1 day cell! Need a more detailed explanation. Biff -----Original Message----- Biff, This worked great but my delima is. B5 to H5 = 7 days I5 to O5 = 7 days S5 = 1 day T5 is hourly rate $8 If my worker works 49 hours the first 7 days. He will be paid overtime for this. If my worker works 15 hours the 2nd 7 days. There is no overtime And the final day 1 hour. There is no overtime. Notice the total hours add up to 65 hours. I made a mistake on how to reference the delima in my last post. Need to calculate the formula in a different way. "Biff" wrote: Hi! Try this: =IF(S580,(S5-80)*A1*1.5+80*A1,S5*A1) Biff -----Original Message----- Duane, I inserted this formula T5 cell. Made A1 = 8 So I inserted 6 into B5 all the way to P5. The formula is giving me a different figure than I should have. Myrna "duane" wrote: something like assuming a1 = hourly rate=8/hr t5=sum(b5:h5)*a1+if(sum(b5:h5)40,sum(b5:h5)-40,0) *a1*1.5+ sum(i5:o5)*a1+if(sum(i5:o5)40,sum(i5:o5)-40,0) *a1*1.5+ p5*a1 B5 to H5 is 1 week, I5 to O5 is 2nd week, P5 is a single day. S5 = total hours. T5 is Gross Pay = total hours x 8 an hr -- duane ----------------------------------------------------- --- ---------------- duane's Profile: http://www.excelforum.com/member.php? action=getinfo&userid=11624 View this thread: http://www.excelforum.com/showthread...hreadid=276115 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display answer only in another cell of one containing a formula | Excel Discussion (Misc queries) | |||
inserting data from a row to a cell, when the row number is specified by a formula in a cell | New Users to Excel | |||
Cell contents vs. Formula contents | Excel Discussion (Misc queries) | |||
I want the results of a formula to show in cell, NOT THE FORMULA! | Excel Discussion (Misc queries) | |||
can i colour a cell on basis of results of a formula e.g clour bl. | Excel Worksheet Functions |