Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 47
Default Payroll Calculations

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




  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Payroll Calculations

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


--
Biff
Microsoft Excel MVP


"Bernie" wrote in message
...
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






  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 221
Default Payroll Calculations

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(C48,8,C4) =IF(D48,8,D4) =IF(E48,8,E4) =IF(F48,8,F4)
=IF(G48,8,G4) 0 0 =SUM(C6:I6) =B6*J6
T1.5 =B6*1.5 =IF(C410,2,(C4-C6)) =IF(D410,2,(D4-D6))
=IF(E410,2,(E4-E6)) =IF(F410,2,(F4-F6)) =IF(G410,2,(G4-G6)) =IF(H42,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















  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 221
Default Payroll Calculations (Take 2)

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(C48,8,C4) =IF(D48,8,D4) =IF(E48,8,E4) =IF(F48,8,F4) =IF(G48,8,G4) 0 0 =SUM(C6:I6) =B6*J6
T1.5 =B6*1.5 =IF(C410,2,(C4-C6)) =IF(D410,2,(D4-D6)) =IF(E410,2,(E4-E6)) =IF(F410,2,(F4-F6)) =IF(G410,2,(G4-G6)) =IF(H42,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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
payroll calculations Bernie Excel Worksheet Functions 1 February 22nd 08 08:32 PM
Need payroll Rao Ratan Singh Excel Discussion (Misc queries) 1 May 9th 07 01:00 PM
how do i set up payroll junelle Excel Worksheet Functions 2 December 18th 06 08:47 AM
Adding payroll stubs payroll calculator Sable New Users to Excel 2 August 5th 06 05:37 PM
Payroll Ted Dawson New Users to Excel 6 February 28th 06 02:35 AM


All times are GMT +1. The time now is 07:33 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"