excel: How do I setup a function to put any numbers over 80 into .

[Guest]

I am trying to setup a payroll spreadsheet for a 2 week period. I would like
once my total for an employee exceeds 80 hours to put the additional hours
over 80 into a seperate overtime pay cell. Any suggestions?

 

[Biff]

Hi!

Try something along these lines:

For straight time:

=MIN(SUM(Time_Range),80)

For OT (over 80):

=MAX(0,SUM(Time_Range)-80)

Biff

 

[Guest]

Nick,

From my understanding, you need to columns. One for total hours under or
equal to 80 and one for total hours over 80. In the <=80 cell use this formula

=IF(SUM(MyRange)>80,80,SUM(MyRange))

and in the >80 cell use

=IF(SUM(MyRange)>80,SUM(MyRange)-80,0)

Replace MyRange with the cells you add to get total hours

Cheers!