TOUGH FORMULA QUESTION - PLEASE HELP!

[Hoov]

I am making a payroll spreadsheet for my work, and I need some help coming
up with a consistent formula. There are three types of hours at work -
Regular, Nights and Weekends, and Overtime. These are in cells N4, O4, and
P4, respectively.
The Nights and Weekends cell (04) is what I need help with. Once the hours
in the "Regular" column hit 40, I need the Nights and Weekends hours to stop
accruing in the Nights and Weekends column, because they are now being paid
out of the Overtime column. I have all of the equations figured out for
Regular and Overtime, I just need Nights and Weekends to stop accruing once
the Regular hits 40 hours.

Here is an example with a hypothetical work schedule:

(B4)Saturday - 10 hours
(C4)Sunday - 10 hours
(D4)Monday - 10 hours
(E4)Tuesday Night - 10 hours
(F4)Wednesday - 10 hours
(G4)Thursday Night - 10 hours
(H4)Friday - 10 hours

OK - according to how my work operates, this worker would stop accruing both
Regular and Nights and Weekend Pay after Tuesday Night, because they are at
40 hours. So Wed, Thurs, and Fri will all be in the Overtime cell. I just
need to know how to tell the Nights and Weekends cell (O4) to stop accruing
hours once the Regular cell reaches 40 hours.

Please help if you are able to, and ask any questions if you need
clarification! Thanks!

 

[Eva]

Hi
I am trying to understand how your spreadsheet looks like
is it like this?
B C D
E.. O
Saturday - 10 hours Sunday - 10 hours Monday - 10 hours etc. Nights&..

You are looking for text formula that will summarie range of B-H?

--


Greatly appreciated

Eva

 

[pmartglass]

Can you explain a little more what you are trying to do. In reading your
post my thoughts are that you have a different rate of pay for
nights/weekends than you do for regular time. You may have an employee that
works both. If I am correct in this than you will also need to know which
pay rate the overtime occured in.

 

[Beverly]

Hi Hoov,

How do you know from one employee to the next whether they've worked Tuesday
vs. Tuesday night? Are you planning to repeat your headers for each employee
that you enter (that is, in row 5, you'd have the days the next employee
worked and then in row 6, you'd have the actual #s)?

If you're open to suggestion, I would suggest that you enter your data like
this:

Date Pay Pd Ending Employee Hours Hours Type
2/13/2010 2/28/2010 Hoov 10 Regular
2/14/2010 2/28/2010 Hoov 10 Regular
2/15/2010 2/28/2010 Hoov 10 Regular
2/16/2010 2/28/2010 Hoov 10 Nights & Weekends
2/17/2010 2/28/2010 Hoov 10 Regular
2/18/2010 2/28/2010 Hoov 10 Nights & Weekends
2/19/2010 2/28/2010 Hoov 10 Regular


Then, to get a summary of the employee's hours by pay period you'd do
something like this (assuming the data above is in cells A1:H8 and the below
are laid out in Columns N-S):

(N1) Employee: (N2) Hoov
(O1) Pay Period Ending: (O2) 2/28/2010
(P1) Regular: (P2)
=IF(SUMIFS($D:$D,$C:$C,$N$2,$E:$E,$P1,$B:$B,$O$2)>40,40,SUMIFS($D:$D,$C:$C,$N$2,$E:$E,$P1,$B:$B,$O$2))
(Q1) Nights & Weekends: (Q2)
=IF(P2=40,0,IF(S2>40,SUMIFS($D:$D,$C:$C,$N$2,$E:$E,$Q1,$B:$B,$O$2)-R2,SUMIFS($D:$D,$C:$C,$N$2,$E:$E,$Q1,$B:$B,$O$2)))
(R1) OT: (R2) =IF(S2<=40,0,S2-40)
(S1) Total: (S2) =SUMIFS($D:$D,$C:$C,$N$2,$B:$B,$O$2)


Hope that helps!

Beverly