Help needed on payroll formula

[Watcher111]

Hi All, I hope someone can help me.

Below (Hopefully) is a payrol register I'm working on. Calidornia payroll
law is such that over 8 hours a day is 1 1/2 times pay over 12 hours is
double time pay. and anything on the 7th consecutive day is double time pay.

What I need is the formula(s) that will figure the REG HRS, OT HRS, and DOT
HRS. I have looked through the books thatI have, and tried a google search
to no avail. Can someone help me either by posting here or emailing me
direct (Remove the nospam from my addy to email me direct). I need to have
this done by tomorrow afternoon. I'm a computer tech not a MS Office person
..

Thanks in advance,
Wayne

Employees Weekly Payroll Register
Week Ending 8/24/03

Employee Pay
Rate Mon Tue Wed Thu Fri Sat Sun Reg
Hrs OT
Hrs DOT
Hrs Gross
Pay
Susan Smith $ 22.00 8.00 6.50 9.00 8.00 8.00
$ -
Kim Johnson $ 29.00 8.00 9.00 9.00 8.00 7.00 8.00
-
Tim Adams $ 27.50 7.00 7.00 7.00 7.00 7.00 7.00 7.00
-
Mark Happy $ 30.00 8.00 8.00 8.00 9.00 8.00
-
Kaz LeStrange $ 6.50 2.00 3.00 16.00 14.00 2.00 10.00 15.00
$ -
Total 0.00 0.00 0.00 $ -

 

[Arvi Laanemets]

Hi

I hope I did understand you correctly. If yes, then when your header row is
the first one, with data starting from row 2, daily hours in columns C:I,
weekly regular, OT, DOT and Gross hours in columns J:M, and no overtime at
weekends:

Reg
J2={M2-K2:L2}
NB! Array function, entered as Ctrl+Shift+Enter
OT
K2={SUM((C2:G2>(8/24))*(C2:G2-(8/24)))-SUM((C2:G2>0.5)*(C2:G2-0.5))}
NB! Array function
DOT
L2={SUM((C2:G2>0.5)*(C2:G2-0.5))+SUM(H2:I2)}
NB! Array function
Gross
M3=SUM(C2:I2)
Pay
N2=B2*(J2+1.5*L2+2*L2)
and copy J2:N2 down for all employees



Arvi Laanemets

 

[StarTrek an beyond]

Arvi,
Watcher111 did not put forth all the information. He sent me the xl file if
you care to look at it I will mail it to you. There is more to it than put
here.

Wayne B
(e-mail address removed)