Payroll Calculation - Help Needed

[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.
Workers at my job can work both Regular hours, and Nights and Weekends.
Therefore, Overtime starts accumulating once the combined totals for Regular
hours and Night and Weekend hours hits 40. I need to tell the Regular and
the Nights and Weekends cells that once they add up to 40, they stop
accruing. Then, the Overtime cell needs to pick up all of the extra hours
over 40.

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 both the Regular (N4) and the Night and Weekend
cell (O4) to stop accruing hours once they add up to 40. The ideal
spreadsheet for the above example would have 10 hrs in the Regular cell (from
Monday), 30 in the Nights and Weekends cell (from Saturday, Sunday, and
Tuesday Night), and 30 in the Overtime cell (for everything after Tuesday
Night).

Please help if you are able to, and ask any questions if you need
clarification! I posted this yesterday, but I didn't explain it well, so
here it is again. Thanks!

 

[Luke M]

You'll need some helper cells, but could do it this way...

Assuming Row 2 designates whether the data is Regular or Night/Weekend, and
A5 is blank or text.

In B5:
=IF(SUM($B$4:B4)<40,B4,IF(SUM($A$5:A5)<40,40-SUM($A$5:A5),""))

Copy across.
Formula for regular time:
=SUMIF(B2:H2,"Regular",B5:H5)
Formula for nights/weekends:
=SUMIF(B2:H2,"Nights/Weekends",B5:H5)
Formula for overtime:
=MAX(SUM(B4:H4)-40,0)

 

[Hoov]

Overtime calculated correctly with this method, but "0" showed up in regular
and nights and weekend. I don't have space for blank cells to be underneath
B4:M4, because all of the workers are listed top to bottom, and there can't
be any gaps between them.

Just to help you understand the look of the cell better:

B2 says "SAT", C2 says "SUN" and so on through the days of the week. N2,
O2, and P2 say Regular, IPR (which is our abbreviation for Nights and
Weekend), and Overtime, respectively. Then, in B4 we put in Saturday's
hours, C5 is Sundays, and so on all the way across to M4. Each Day of the
Week has a day cell, and a night cell. For example, Monday regular is D4,
and Monday night is E4. So, I have it all broken out as it should be. I
just need IPR and Regular to stop calculating once they combine to hit 40
hours, and Overtime to pick up all of the hours over 40.

But, I can't have blank cells in row 5, because that is the next guy's
weekly hours.

Thanks so much for your help, hopefully this makes sense.

 

[John]

Hi Hoov
I'm not sure on your setup but this will add up to 40 and stop.
=MIN(40,SUM(A5:G5)) . Just adjust range to your needs.
Luke M. formula for the overtime will complet the calculation.
HTH
John