help in combining formulas

  • Thread starter Thread starter Thaddeus
  • Start date Start date
T

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
 
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)
 
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
 
Back
Top