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

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

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

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!!! :)

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