Adding Multiple Lines when the Function is Satisfied

[pugsly8422]

I am having a few problems trying to come up with a function that can satisfy
this problem. Sometimes one employee may appear under more than one group
(bartender, barback, waitstaff, etc.). I need to insert a formula that will
check the other groups to see if this employee worked any hours in another
group that week (I would assume a VLOOKUP would work), and then distribute
the overtime hours accordingly. Right now, the function I am using is:

=IF(A2="","",MAX(0,SUMPRODUCT(($A$2:$A$53=A2)*ISNUMBER($D$2:$H$53),($D$2:$H$53))-40))

It is hard to explain what I am trying to do, but I'll do my best. This is
a timesheet and sometimes the same employee will appear in 2 different places
in a single week. It may look something like this:

Bartender: Mon Tue Wed Thu Fri Sat Sun Total O/T
Bob 8 5 6 9 2 28 2

Barback: Mon Tue Wed Thu Fri Sat Sun Total O/T
Bob 3 3 2 8 3 12 7

What this is saying is how many hours he worked at each position on each
day, and because there are different pay rates for each position, we have to
seperate them. Also, once someone reaches 40 hours total, they begin earning
overtime. I need a function that will check the other categories (bartender,
barback, waitstaff, host, training) to see if the employee worked any other
hours. If they did work additional hours I need it to add the hours up a day
at a time so that when it reaches 40 they will begin earning overtime. In
the example above, Bob had 40 hours halfway through Friday, so everything
after that was overtime. He worked 28 "normal" hours and 2 "overtime" hours
bartending and 12 "normal" hours and 12 "overtime" hours barbacking.

The only thing I can come up with is:

if(bobs name appears more than once,if(bob #1 monday hours+bob #2 monday
hours+bob #3 monday hours+bob #4 monday hours+bob #5 monday hours>40,bob #1
monday thru sunday hours+bob #2 monday thru sunday hours+bob #3 monday thru
sunday hours+bob #4 monday thru sunday hours+bob #5 monday thru sunday
hours-40,if(bob #1 monday thru tuesday hours+bob #2 monday thru tuesday
hours+bob #3 monday thru tuesday hours+bob #4 monday thru tuesday hours+bob
#5 monday thru tuesday hours>40,bob #1 monday thru sunday hours+bob #2 monday
thru sunday hours+bob #3 monday thru sunday hours+bob #4 monday thru sunday
hours+bob #5 monday thru sunday hours-40,if(bob #1 monday thru wednesday
hours+bob #2 monday thru wednesday hours+bob #3 monday thru wednesday
hours+bob #4 monday thru wednesday hours+bob #5 monday thru wednesday
hours>40,bob #1 monday thru sunday hours+bob #2 monday thru sunday hours+bob
#3 monday thru sunday hours+bob #4 monday thru sunday hours+bob #5 monday
thru sunday hours-40..........,if(bob #1 monday thru sunday hours+bob #2
monday thru sunday hours+bob #3 monday thru sunday hours+bob #4 monday thru
sunday hours+bob #5 monday thru sunday hours>40,bob #1 monday thru sunday
hours+bob #2 monday thru sunday hours+bob #3 monday thru sunday hours+bob #4
monday thru sunday hours+bob #5 monday thru sunday hours-40)))))))

I doubt that will fit, and am thinking there must be an easier way to do it.
After spending over a week on this I am about ready to pound my head on the
wall, hopefully someone can assist me, it will be greatly appreciated. If
there is any other information you need feel free to ask. I could also send
you the workbook if that would make it easier.

Thank you in advance.

 

[Sheeloo]

Pl. send the file (with actual data) to me at (e-mail address removed)