Overtime computation and lunch break formula with IF and AND

[navychef]

Hi - I'm having a heckuva time trying to create a formula that wil
enable me to create an employee work schedule that will alert th
manager to scheduled overtimes. The criterium are:

- if employee works 6-8 hours, they get .5 hour lunch break that doe
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 fro
the daily total hours?

Assume that start time is A3, end time is B3, and total daily hours i
B4

Once I have that, I can easily total actual paid hours for the week.

THANK YOU THANK YOU THANK YOU!!! :

 

[Bob Phillips]

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

--

HTH

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

 

[navychef]

AWESOME! Thank you SO MUCH!

That is REALLY perfect, I wasn't anywhere close to it! Formula work
great. I just added in an = to also subtract the .5 when it's equal t
6 hours.

THANK YOU!!!!!!!!!!!

Bo

 

[Gord Dibben]

Check out Chip Pearson's timesheet site.

http://www.cpearson.com/excel/overtime.htm


Gord Dibben Excel MVP

 

[Bob Phillips]

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