Timesheet help

[daveburke114]

http://rapidshare.com/files/434458258/Test_Timesheet.xlsx

Hi, I've been asked by my dad to try and automate his staff rota on excel. I've attached the file i've got so far.

Its been a fair few years since ive used excel and this ones really hurting my head now haha! Basically this is what he needs it to do:

The timesheet on the top left will be filled out by the manager.
A member of staff's name will be put under the day and shift they are doing.

All members of staff are payed different amounts which is defined in the table on the right.

The table on the bottom will (hopefully) be automatically filled in with the correct amount of hours and the total pay for that week.


I've done lots of reading up and thought maybe an IF or maybe LOOKUP formulas would work, but I just cant work it out at all.

I've tried sifting through the code of loads of timesheet templates but none of them seem to do what I want so i'm struggling!

Could anyone point me in the right direction or tell me if its even possible. If its not could anyone suggest a better way of going about it? (ive even tried trials of paid software and non of them seem to cover this either.

I've had a search of the forums but cant find anything

Thanks in advance
Dave

 

[crook_101]

Hi Dave,

tried to download the sheet but cannot get access to the site - stumpted at the mo - can you upload sheet to this site using one of the methods above eg excel genie??

 

[daveburke114]

hi, thanks for your quick response
Could you tell me how to upload files to this site? i had a look at the faq and could only see the html link. i searched excel genie too and came up with nothing. im not having a very successful day haha.

If not I will post it to a different webhost and put a new link up when i get back in the office.

cheers
dave

 

[crook_101]

Hi Dave,

This link will get you sorted with jeanie

http://www.excel-jeanie-html.de/index.php?f=1

A lot of people use this as it also shows formulas on the worksheets which can be quite handy when asking/answering questions.

Ian

 

[daveburke114]

thanks crook_101
I think ive done it now:
http://www.submitdesign.co.uk/files/timesheets/timesheet.htm
http://www.submitdesign.co.uk/files/timesheets/hourlypay.htm
http://www.submitdesign.co.uk/files/timesheets/totalpay.htm


All three of these tables are on one sheet but jeanie wouldnt let me export them all at once!

Do these display ok and make sense?

There are two members of staff on most of the shifts except the kitchen and housekeeping, but on some days there may be!

 

[crook_101]

Hi Dave,

This is a starter for you - copy the formulas down from cells E20:G20 and this will get you started - probably need to run through some of the stuff with you so please shout if you have any questions - for the formulas to work you may have to change you sheets to match the below or change the formula to match your sheet.

Post back if you have any queries
Ian.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	TIMESHEET FOR WEEK:															Hourly Pay
2
3	Shift	Hours	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday				Staff	08 :00 - 17:00	17:00 - 22:00	Asleep	Awake	Housekeeping	Kitchen
4	08 :00 - 17:00	9	Jan	Phil									Jan	8.50	8.50	4.50	8.50	8.50	8.50
5	08 :00 - 17:00	9	Tom	Jenny									Tom	6.20	6.20	4.00	6.00	5.50	5.50
6	17:00 - 22:00	5	Phil	Fred									Phil	6.00	6.00	3.80	5.80	5.50	5.50
7	17:00 - 22:00	5	Jenny	Sam									Jenny	6.00	6.00	3.80	5.80	5.50	5.50
8	Asleep	10	Fred	Linda									Fred	6.00	6.00	3.80	5.80	5.50	5.50
9	Asleep	10	Sam	Jayne									Sam	6.00	6.00	3.80	5.80	5.50	5.50
10	Awake	10	Linda	Frank									Linda	6.00	6.00	3.80	5.80	5.50	5.50
11	Awake	10	Jayne	Bill									Jayne	6.00	6.00	3.80	5.80	5.50	5.50
12	Housekeeping	10	Dec	Jan									Frank	5.40	5.40	3.50	5.00	5.00	5.00
13	Kitchen	10	Bill	Tom									Bill	5.20	5.20	3.50	5.00	5.00	5.00
14	Kitchen	10
15
16
17					Total Pay
18
19					Total Shifts	Total Hours	Pay Due
20				Jan	2	19	161.50
21				Tom
22				Phil
Sheet1

 

[daveburke114]

hi crook_101

thanks for your help!

just a quick question;
the spreadsheet works a treat, but as i copied the forumla in G20 down, it worked for half the staff but then gave a total pay of £0 for others even though they had 'worked' 10 hours?

any ideas?


also, say a new momber of staff joins the books, and i add them to column M, which other bits of formula do i need to change?

cheers
dave

 

[daveburke114]

--- sorry for the double post it only let me edit within ten mins! ---

hi crook_101

thanks for your help!

just a quick question;
the spreadsheet works a treat, but as i copied the forumla in G20 down, it worked for half the staff but then gave a total pay of £0 for others even though they had 'worked' 10 hours?
*Ahh, right so when i drag it down it bumps up all cell referances by one, but if i copy and paste the code into each one it only referances the first name and so give mes their wages for all staff.
Is there any way to keep the cell referancing the same for the timesheet, but change the staff referance?
I hope that makes sense? ie so I dont have to go through all the segments of code and update the cell that referances the staff name?



also, say a new member of staff joins the books, and i add them to column M, which other bits of formula do i need to change? is there a best way to add and delete new staff?

cheers
dave