Payroll Calculations

[Bernie]

I know a few of you have helped me in the past and the sugestions have been
great but I'm still having trouble.

What I need to do is calculate a week of time including sick and vacation
where each day has it's own cell. ie S M T W R F S, and if anyone works over
10 hours in a day it is automaticly added to overtime. for example if a
person worked 8.5reg + 8sick + 8vacation + 12.5 reg + 8.5reg it would give me
the output in 3 columns 32 hours regular 5.5 hours overtime 16 hours
vacation/sick

Thank you in advance for the help

 

[T. Valko]

Your explanation isn't consistent:

works over 10 hours in a day it is...overtime.
8.5reg ...12.5 reg ... 8.5reg
5.5 hours overtime

If 5.5 hrs OT is correct then the criteria must be: works over 8 hours in a
day = OT

How do you designate if a person is sick or on vacation?

Also, how do you arrive at 32 reg hrs? Shouldn't it be:

24 reg hrs ... 5.5 OT hrs ... 16 s/v

 

[NoodNutt]

G'day Bernie

Something for you to consider when setting up your workbook, here is an
example for you to play with.

you will have to reproduce it to match your specifications, but it may give
you a starting point

HTH
Mark.


1. Formula view

Employee Jack

Rate: Mon Tue Wed Thu Fri Sat Sun Total $
Hours 10 12 0 9 0 4 4 =SUM(C4:I4)
Status Normal Normal Sick Normal Holiday Overtime Overtime
T1.0 16 =IF(C4>8,8,C4) =IF(D4>8,8,D4) =IF(E4>8,8,E4) =IF(F4>8,8,F4)
=IF(G4>8,8,G4) 0 0 =SUM(C6:I6) =B6*J6
T1.5 =B6*1.5 =IF(C4>10,2,(C4-C6)) =IF(D4>10,2,(D4-D6))
=IF(E4>10,2,(E4-E6)) =IF(F4>10,2,(F4-F6)) =IF(G4>10,2,(G4-G6)) =IF(H4>2,2,0)
0 =SUM(C7:I7) =B7*J7
T2.0 =B6*2 =C4-(C6+C7) =D4-(D6+D7) =E4-(E6+E7) =F4-(F6+F7) =G4-(G6+G7)
=H4-(H6+H7) =I4-(I6+I7) =SUM(C8:I8) =B8*J8
Sick 16 8 =SUM(C9:I9) =B9*J9
Hol 16 8 =SUM(C10:I10) =B10*J10
Long 16 =SUM(C11:I11) =B11*J11


Total =SUM(K6:K13)


2. Actual View


Employee Jack

Rate: Mon Tue Wed Thu Fri Sat Sun Total $
Hours 10.00 12.00 0.00 9.00 0.00 4.00 4.00 39.00
Status Normal Normal Sick Normal Holiday Overtime Overtime
T1.0 16.00 8.00 8.00 0.00 8.00 0.00 0.00 0.00 24.00 384.00
T1.5 24.00 2.00 2.00 0.00 1.00 0.00 2.00 0.00 7.00 168.00
T2.0 32.00 0.00 2.00 0.00 0.00 0.00 2.00 4.00 8.00 256.00
Sick 16.00 8.00 8.00 128.00
Hol 16.00 8.00 8.00 128.00
Long 16.00 0.00 0.00


Total 936.00

 

[NoodNutt]

G'day Bernie

Something for you to consider when setting up your workbook, here is an example for you to play with.

you will have to reproduce it to match your specifications, but it may give you a starting point

HTH
Mark.


1. Formula view

Employee Jack

Rate: Mon Tue Wed Thu Fri Sat Sun Total $
Hours 10 12 0 9 0 4 4 =SUM(C4:I4)
Status Normal Normal Sick Normal Holiday Overtime Overtime
T1.0 16 =IF(C4>8,8,C4) =IF(D4>8,8,D4) =IF(E4>8,8,E4) =IF(F4>8,8,F4) =IF(G4>8,8,G4) 0 0 =SUM(C6:I6) =B6*J6
T1.5 =B6*1.5 =IF(C4>10,2,(C4-C6)) =IF(D4>10,2,(D4-D6)) =IF(E4>10,2,(E4-E6)) =IF(F4>10,2,(F4-F6)) =IF(G4>10,2,(G4-G6)) =IF(H4>2,2,0) 0 =SUM(C7:I7) =B7*J7
T2.0 =B6*2 =C4-(C6+C7) =D4-(D6+D7) =E4-(E6+E7) =F4-(F6+F7) =G4-(G6+G7) =H4-(H6+H7) =I4-(I6+I7) =SUM(C8:I8) =B8*J8
Sick 16 8 =SUM(C9:I9) =B9*J9
Hol 16 8 =SUM(C10:I10) =B10*J10
Long 16 =SUM(C11:I11) =B11*J11


Total =SUM(K6:K13)


2. Actual View

Employee Jack

Rate: Mon Tue Wed Thu Fri Sat Sun Total $
Hours 10.00 12.00 0.00 9.00 0.00 4.00 4.00 39.00
Status Normal Normal Sick Normal Holiday Overtime Overtime
T1.0 16.00 8.00 8.00 0.00 8.00 0.00 0.00 0.00 24.00 384.00
T1.5 24.00 2.00 2.00 0.00 1.00 0.00 2.00 0.00 7.00 168.00
T2.0 32.00 0.00 2.00 0.00 0.00 0.00 2.00 4.00 8.00 256.00
Sick 16.00 8.00 8.00 128.00
Hol 16.00 8.00 8.00 128.00
Long 16.00 0.00 0.00


Total 1064.00