Pl. send the file (with actual data) to me at
(E-Mail Removed)
"pugsly8422" wrote:
> 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.
>
Similar Threads
