calculation overtime wages

  • Thread starter Thread starter cefpe
  • Start date Start date
C

cefpe

Consider that an employee can work more that 8 hours in one day (say 8.
hours) or that he can work more than 40 hours in one week and that h
will be paid time and a half for overtime, how do I write an equatio
to sum the hours correctly and pay him the correct wages? Or he coul
work less than 40 hours but still have overtime hours each day. se
below

M T W T F S S Total

8 8.5 8.5 8 8 41 total hours but .5 hours o
Tuesday and Wednesday are overtime hours
 
Regular time hours:

=SUM(IF(A2:G2<8,A2:G2,8))

Array-entered, meaning press ctrl/shift/enter.

OT hours:

=SUM(IF(A2:G2>8,A2:G2-8,""))

Also array-entered.

HTH
Jason
Atlanta, GA
 
Here is one solution. Not very flexible, but:
Say you had columns devoted to days as follows: col D = Monday, Col E =
Tuesday etc
and say your data was in row 19, then:

=SUM(D19:J19+(D19:J19-8)*(D19:J19>8)*0.5)

entered as an array formula (Contol+Shift+Enter when entering the formula)

This would give you the total number of hours to be multiplied by the
standard pay scale.
 
HTH,
The formulae do not take into consideration that Sat. or Sun. are O.
wages, It works for the 5 days of the week but does not work for th
weekend.
How do I do this?
C
 
I haven't seen the formula to which you refer, but it sounds like you need an
additional IF test that checks the WEEKDAY.
 
=SUM(1.5*D19:J19-0.5*(IF(D19:J19<8,D19:J19,8))*(COLUMN(D19:J19)<COLUMN(I19)))

Array entered (Control/Shift/Enter)

Where D19 contains Monday's hours, E19 Tuesday etc
Multiply the above by the standard pay rate
 
Slight improvement provided by Frank Kabel

=1.5*SUM(D19:J19)-0.5*SUM(IF(D19:H19<8,D19:H19,8))
Array entered

--
Return email address is not as DEEP as it appears
Jack Schitt said:
=SUM(1.5*D19:J19-0.5*(IF(D19:J19<8,D19:J19,8))*(COLUMN(D19:J19)<COLUMN(I19)))

Array entered (Control/Shift/Enter)

Where D19 contains Monday's hours, E19 Tuesday etc
Multiply the above by the standard pay rate
Click to expand...
 
Back
Top