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 are:

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

=hours_worked-(hours_worked>6)*.5-(hours_worked>8)*.5

--

HTH

RP
(remove nothere from the email address if mailing direct)


"navychef" <navychef.1zp0rm_1133997904.9112@excelforum-nospam.com> wrote in
message news:navychef.1zp0rm_1133997904.9112@excelforum-nospam.com...
>
> 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 are:
>
> - 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
>

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

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
<navychef.1zp0rm_1133997904.9112@excelforum-nospam.com> 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 are:
>
>- 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!!!

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" <navychef.1zp2dz_1134000004.7196@excelforum-nospam.com> wrote in
message news:navychef.1zp2dz_1134000004.7196@excelforum-nospam.com...
>
> 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
>