calculation overtime wages

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
 
J

Jason Morin

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
 
C

cefpe

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
 
M

Myrna Larson

I haven't seen the formula to which you refer, but it sounds like you need an
additional IF test that checks the WEEKDAY.
 
J

Jack Schitt

=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
 
J

Jack Schitt

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...
 
Top