Payroll....Help!!!!

[Nuyorican]

I have to put a payroll sheet together for approximately 20 employees. I need some advise on the content. An employee might work 4 hours and take 4 hours with no pay...OR....one might work 4 hours and take 4 hours sick time. There are approximately 17 choices that have to be listed somewhere.

001 Regular time
002 Overtime - Reg
003 Vacation
004 Sick
005 Holiday
006 Shift Pay
007 Standby Pay (On Call)
008 Umpires Pay
009 Floating Holiday
010 Overtime -Shift Pay
011 Bereavement
012 Workers Comp - Pay
013 Military Leave
014 Civil Leave
015 New Born Leave
016 Family Medical Leave - Vac Pay
017 Family Medical Leave - Sick Pay
018 Sick Bank Pay
NOP No Pay

I have to be able to account for whatever combination an employee might consider in an 8 hour day.

Someone please give me a starting point. I've put payroll time sheets together in the past, but something very basic. Any suggestions would be appreciated. Thanks.
Benny

 

[DonkeyOte]

put ee's in Column A row 3 onwards

place 17 types (name) in Row 1 Col B onwards
place 17 codes (for names) in Row 2 Col B onwards

Then enter number in matrix as required - you can then if necessary do calcs in the 19th column - we can help with that too.

Once set up (and it's viable for you) come back with some sample data and what the results should be etc...

 

[Nuyorican]

OK...As you suggested, I have the employees running from A3 thru A13; the payroll types (names) are in B1 thru T1, and the payroll codes (001, 002, etc.) are in B2 thru T2. If an employee works 4 hours Regular (001) and takes 4 hours Sick time (004), I have to be able to show that breakdown for the day. I have to be able to document the day, regardless of how it is accomplished (another example: I work 8 Regular hours (001) and 2 Overtime) I have to reference the 8 and 2.
Thank you
Benny

 

[DonkeyOte]

OK well say employee is Joe Bloggs - appears in A12

does 4 hours normal (001) and 4 hours sick (004)

then can you not enter 4 in B12 (normal) and 4 in E12 (sick)

Then if you need to calculations etc you can use Column U - ie to count total hours worked for Joe Bloggs then U12 = Sum(B12:T12) [answer 8]

Similarly you can use this data to do other calcs - say if you need to work out pay and each category (001 etc) has a rate per hour then you can do that etc... I hope we're on the right lines with this.

Provide an example of what you would like to do exactly.

 

[Nuyorican]

OK, brother; I'll explain what I did yesterday. I had the employess running the same way (column A3 down); Sunday thru Saturday running from B2 thru B8 and the totals at the end. I had a drop down list on each day giving me a choice of "001-8, 002-8, 003-8, etc, etc.". In the totals cell I had "=RIGHT(B3,2)+RIGHT(C3,2), etc, etc", so I could add only the "8", representing the 8 hour day.

Here comes the problem, they tell me they have to be able to pay in half hour increments (ie, 7.5 hours). In addition, they want the breakdown like I mentioned earlier.

Can I incorporate the daily workings (Sun thru Sat) in this sheet or do I need something different???? Previous setup had Week 1 and Week 2 below. I hope I have made myself clear.
Thank you,
Benny

 

[Northerner]

If it's any help, =RIGHT(A1,(LEN(A1)-FIND("-",A1,1))) will return your hours worked using your format "001-8" wether parts of hours are used, as the formula looks for the "-" which you separate your code and hours with.

 

[DonkeyOte]

OK - well you do have some issues trying to place an entire week on one sheet - reason being you have n number of employees, 18 or so types of work (001 etc) and 7 days data so you really need 7*18 rows/columns per employee so as to have the flexibility of someone doing 4 hours of this, 2 hours of that, 1 hour of this etc per day.

There are better ways perhaps of doing this (using Access for example) BUT you can still do it in Excel without it getting too obtuse.

I didn't realise it was for a week so I am going to reverse my original advice and say that you're best bet would be to put the employees in row 1 Column c onwards

then in A2 - enter the first day eg "sunday

then in B2 enter 001 - .... in B3 enter 002 - .... and so on...
when all Sunday's stuff has been entered, in the next row Column A enter Monday, in the adjacent column enter 001 - ... and so on (obviously you can copy 001... to 018 from Sunday for all other days ... this is cumbersome but to my mind is the only viable way of doing it on one sheet.

If you then go to C2 - and Freeze Panes - you should find you can move around the rows columns quite easily without losing track of who you're working with and what type of work you're entering time for.

Once this is set up it's pretty straightforward to generate a summary sheet I would say.

Let me know.

Maybe someone else will have a better solution to this - ie. keeping it all on one sheet and not using VBA.

The other way that comes to mind would be to say - set a maximum of different entries you're likely to have to post for one day (ie 4 types of work) and assign a code to each of these types. You can then enter S - 4 (s for sick) - and thus you'll reduce the size of your matrix quite considerably (no need for 18 types of work per day - just 4).

 

[Nuyorican]

You know what they say, "a little knowledge is dangerous". I have working knowledge of Access; so what do you suggest??? the reason I was geared toward XL was because the main department that receives the payroll reports from different areas, is using XL. I'm only doing an internship at this organization for the summer, but I might be able to sway them to accept the Access as a subsititute.

Thanks,
Benny

 

[plettieri]

Hello:

A little late for my input but I will add my 2 cents..... for what it is worth.........I work with payroll data from time to time and there is always some summary requirement needed by someone in a way that was not initally planned. (in my enviroment...payroll statutory requirements, insurance issues, government filings, in house department costing application, direct vs. indirect labor, etc.)

It seems that a data base with all the components of the raw data (hours, rates, your codes, etc.) kept in data base gives you a lot more flexibility to arrange the data in different arrangements later on.

good luck
plettieri