help in combining formulas

[Thaddeus]

This is for my employees payroll, if they work "under 60"
hours their overtime begins at 45hrs, if they work "over
60" thier overtime begins at 40.

the formulas I'm useing now are:
under 60
=MIN(45,$C$17)*$B$2,=MAX($C$17-45,0)*$D$2
over 60
=MIN(40,$C$17)*$B$2,=MAX($C$17-40,0)*$D$2

as a result I have had to keep 2 time sheets per employee
(in excel) and anaylize thier hours indiviualy.

Can I combine these formulas so that the calculations will
be correct under and over 60hrs???

Thanks for any help at all

Thaddeus

 

[Max]

To combine, one way is to use IF() to check the hours worked
(presume this in C17), as in:

: =IF($C$17<=60,<formulae_for_60hrs_or_under>,<formulae_for_over_60hrs>)

For example something along the lines of:

For your MIN formulae:

: =IF($C$17<=60,MIN(45,$C$17)*$B$2,MIN(40,$C$17)*$B$2)

For your MAX formulae:

: =IF($C$17<=60,MAX($C$17-45,0)*$D$2,MAX($C$17-40,0)*$D$2)

 

[Peo Sjoblom]

just use IF, something like

=if(C17<60,MIN(45,$C$17)*$B$2,MIN(40,$C$17)*$B$2)

 

[Andy Brown]

Say you have someone's total hours in B2.

=IF(B2>60,(B2-40)*rate,IF(B2>=45,(B2-45)*rate,0))

Notwithstanding in some countries it's illegal to work >60 hours in one
week.

HTH,
Andy