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
"Hoov" wrote in message
...
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!