|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
December 7th, 2005, 11:23 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Overtime computation and lunch break formula with IF and AND
Hi - I'm having a heckuva time trying to create a formula that will enable me to create an employee work schedule that will alert the manager to scheduled overtimes. The criterium a - if employee works 6-8 hours, they get .5 hour lunch break that does not get factored into their total hours for the week - if employee works 8 hours or more, they get 1.0 hour lunch break How do I write the formula to recognize if they worked = 6 (but 8) and =8, and have either .5 or 1.0 hours as appropriate deducted from the daily total hours? Assume that start time is A3, end time is B3, and total daily hours is B4 Once I have that, I can easily total actual paid hours for the week. THANK YOU THANK YOU THANK YOU!!!  -- navychef ------------------------------------------------------------------------ navychef's Profile: http://www.excelforum.com/member.php...o&userid=29457 View this thread: http://www.excelforum.com/showthread...hreadid=491668 
|
|
#2
December 7th, 2005, 11:47 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Overtime computation and lunch break formula with IF and AND
=hours_worked-(hours_worked6)*.5-(hours_worked8)*.5
-- HTH RP (remove nothere from the email address if mailing direct) "navychef" wrote in message ... Hi - I'm having a heckuva time trying to create a formula that will enable me to create an employee work schedule that will alert the manager to scheduled overtimes. The criterium a - if employee works 6-8 hours, they get .5 hour lunch break that does not get factored into their total hours for the week - if employee works 8 hours or more, they get 1.0 hour lunch break How do I write the formula to recognize if they worked = 6 (but 8) and =8, and have either .5 or 1.0 hours as appropriate deducted from the daily total hours? Assume that start time is A3, end time is B3, and total daily hours is B4 Once I have that, I can easily total actual paid hours for the week. THANK YOU THANK YOU THANK YOU!!! -- navychef ------------------------------------------------------------------------ navychef's Profile: http://www.excelforum.com/member.php...o&userid=29457 View this thread: http://www.excelforum.com/showthread...hreadid=491668
|
|
#3
December 7th, 2005, 11:59 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Overtime computation and lunch break formula with IF and AND
AWESOME! Thank you SO MUCH! That is REALLY perfect, I wasn't anywhere close to it! Formula works great. I just added in an = to also subtract the .5 when it's equal to 6 hours. THANK YOU!!!!!!!!!!! Bob -- navychef ------------------------------------------------------------------------ navychef's Profile: http://www.excelforum.com/member.php...o&userid=29457 View this thread: http://www.excelforum.com/showthread...hreadid=491668
|
|
#4
December 8th, 2005, 12:33 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Overtime computation and lunch break formula with IF and AND
Check out Chip Pearson's timesheet site.
http://www.cpearson.com/excel/overtime.htm Gord Dibben Excel MVP On Wed, 7 Dec 2005 17:23:12 -0600, navychef wrote: Hi - I'm having a heckuva time trying to create a formula that will enable me to create an employee work schedule that will alert the manager to scheduled overtimes. The criterium a - if employee works 6-8 hours, they get .5 hour lunch break that does not get factored into their total hours for the week - if employee works 8 hours or more, they get 1.0 hour lunch break How do I write the formula to recognize if they worked = 6 (but 8) and =8, and have either .5 or 1.0 hours as appropriate deducted from the daily total hours? Assume that start time is A3, end time is B3, and total daily hours is B4 Once I have that, I can easily total actual paid hours for the week. THANK YOU THANK YOU THANK YOU!!!
|
|
#5
December 8th, 2005, 07:06 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Overtime computation and lunch break formula with IF and AND
Really, that surprises me. I would have thought that once you go over 6
hours it would mean that a lunch-break was taken, otherwise not. Still, no matter, you got it working which is the important thing. Regards Bob "navychef" wrote in message ... AWESOME! Thank you SO MUCH! That is REALLY perfect, I wasn't anywhere close to it! Formula works great. I just added in an = to also subtract the .5 when it's equal to 6 hours. THANK YOU!!!!!!!!!!! Bob -- navychef ------------------------------------------------------------------------ navychef's Profile: http://www.excelforum.com/member.php...o&userid=29457 View this thread: http://www.excelforum.com/showthread...hreadid=491668
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
