Time sheet tweaking, weeknum, and pivot tables

**gortw4j** · 11-11-2012, 06:13 PM

Good day,

I'm new here, and I feel somewhat frustrated, because I've spent the whole weekend trying different formulas, and codes, but need serious help now, please.

I have a few questions, but will try to spell them out clearly.

First of all, I need to tweak the time sheet that our small company uses, so that all of them gets calculated the same, so I'm trying to make a template which each employee can just copy, paste, and use.

These are the main columns that I need help with:

	A	B	C	D	E
	Date	Time Start	Time End	Total Hrs	Project
1	Mon 12 Nov 2012	8:00	14:00	6	ES064
2		14:00	17:00	3	Drafting
3		17:00	18:00	1	Admin
4	Tue 13 Nov 2012	7:30	10:00	2.5	Admin
5		10:00	17:00	8	ES064
6
7	Mon 19 Nov 2012	8:00	16:00	8	ES064

Now, I use 3 columns to the right of the sheet, which have white text, so just for reference, to return 3 columns: =WEEKNUM from the Date Column, Hours, and Project.

I've set up two pivot tables, one that shows how many hours were spent per project per week, and another per month.

The main thing I'm struggeling with now is, because there are blanks in the DATE column, the weeknum does not return for those cells.

Also to keep in mind, that between weeks, we leave one row blank.

So, the formula I want, must do this, I just don't know how to write it:
=IF((A2 is blank, and A3 is filled), look up in the column, and return the weeknum of the last filled cell)), namely A1.

So in the case of row 6 being empty, it will not return anything, because B6 is empty.

I want to drag that down to the end of the sheet, at the moment just about 60 rows.

Then finally, at the bottom of the sheet, I've put the two pivot tables. I want them to update as the data is being inserted into the time sheet. And they must be protected from any editing, by any employees...

I would really appreciate any help, Thanks.

Greetings,
Christiaan (From South Africa)

**dredwolf** · 11-11-2012, 07:43 PM

could you upload a sample workbook to make it a little easier to help?

Add a File - click advanced (next to quick post), scroll down until you see "manage file", click that and select "add files" (top right corner). click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

**gortw4j** · 11-12-2012, 12:24 AM

Thank you so much.

Please read the red text in the document, It's just to give you some info.

Hope that makes it easier.

Thanks.

**FDibbins** · 11-12-2012, 01:44 AM

for a start, would it help if, in B10, you entered =B9, and then copied that all the way down. That way, when you enter a new date, it will overwrite that specific formula, but the table on the right will still pick up the "empty" date cells?