calculation overtime wages

[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

 

[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

 

[Jack Schitt]

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.

 

[cefpe]

Gentlemen,
Thanks for your response. I'll let you know how this works.
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

 

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

 

[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

 

[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

=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