Timesheets and total time against projects

[krazyderek]

OK we use a 52 sheet excel work book to track our hours against project numbers 08XX for 2008, 09XX for 2009, 10XX for 2010 etc. Each sheet is named by the week for example May-15.

The challenge is that everyone works on different projects and doesn't use a static list of project numbers (since the list grows everytime a client walks in the door) to put their time against but rather inputs the project number in one cell, then the name of the project in the next cell, then the time for each day which is then totaled for the row (week) in another cell at the end all on the same row.

I'm wondering how we can setup a script to automatically see all the 0828 (for example) project lines in every sheet and grab the total time for that week on that project then give me one big master list of time vs every project that person has worked on in one "total's" sheet at the end of the work book. As it stands it takes about half an hour to go through everyone's excel time sheet work books to prepare an invoice.

Idealy this Total's sheet would list any used project numbers in one column, then have a column for each weekly sheet into which it would place the total time against that project for that sheet so we could still track when the hours were being put against the project but it would take a fraction of the time it does now.

I can post an example of our time sheet book if that helps.

 

[gecs]

Is something like this of any help to you?

Excel Workbook
	A	B	C	D	E	F	G
1	PROJECT	Sheet1	Sheet2	Sheet3	Sheet4	Sheet5	TOTAL
2	0823	40	40	57	54	40	231
3	0834	83	68	80	64	93	388
4	0901	48	69	34	55	32	238
Sheet6

"Sheet1" to "Sheet5" are the names of your weekly worksheets (change them according to the names you're using) and in the SUMPRODUCT formulas you have to replace the range "$A$2:$A$200" with the range where the project numbers reside in every worksheet and the "$G$2:$G$200" range with the range where the weekly totals reside in every weekly worksheet. Keep in mind that all ranges used in each SUMPRODUCT formula must have the same size.

 

[krazyderek]

That's pretty good, it works when i use text names for the sheet names but when i try using the 02-Jan or Jan-02 system denoting the week ending date (friday) the sheet name is fine, but the cell converts the date to 1/2/2009 and then they don't match and the formula doesn't work

I assume this also requires the project numbers in the first column to be input manually ? So if you don't think to put it in you'll never know this person has time on a project. Is there anyway to put an IF statement in so that the project number auto populates from numbers it finds in the cells and then doesn't do anything if they're blank?

For example, one employee, might work on 0828, 0822, and 0935 on one week (one sheet), then work on 0935, 0822, and 0845 the next week(another sheet), well we don't want to have to generate a complete list of every project number from 0800 to 0999 just to find that out this employee has worked on 4 projects so far this year and the time they have against them.

Also after some tinkering i see that the project numbers have to remain static, when i put them in out of order on every sheet the number's don't work out. It seems to randomly get the data regardless of what project number is on that row ?

 

[gecs]

About the sheet name problem: before writing Jan-02 or 02-Jan format those cells as text. You may also write down 'Jan-02 instead of Jan-02. Also the formulas need a minor change, like in the example below.

Excel Workbook
	A	B	C	D	E	F	G
1	PROJECT	Jan-02	Feb-12	Sheet3	Sheet4	Sheet5	TOTAL
2	0823	40	40	57	54	40	231
3	0834	83	68	80	64	93	388
4	0901	48	69	34	55	32	238
5	0835	25	0	0	0	18	43
6	0836	21	0	0	25	0	46
7	0837	23	0	0	0	0	23
8	0902	0	18	0	0	0	18
9	0903	0	0	21	0	0	21
10	0824	0	0	0	16	0	16
Total

After I wrote my first post on the subject I also had this very clear: the problem will be to gather the project numbers in the Total sheet. Not only that is difficult to gather them from all 52 weekly sheets, but the total project numbers list shouldn't have duplicates in it. Not that achieving something like this will be impossible, but it's time consuming even with a macro. Instead there is an elegant solution which might save time and avoid errors: create a projects list associated to a name in the workbook and use data validation rules based on that list for the column that houses the project number in every weekly sheet. In this case, if the project number is not in the list the user has to choose from, he has to add it in the list first. The project number will appear instantly in the list because a dynamic range will be associated to that list.

So, try something like this: first add a sheet named Projects; starting with A1 type down in that A column some project numbers; open the Name Manager (Ctrl+F3) and add a name, say, projects_no and in the "Refers to" area use the following formula:

=OFFSET(Projects!$A$1,0,0,COUNTA(Projects!$A$1:$A$100),1)

presuming somebody will not work on more than 100 projects in one year - if this is not the case, increase that number to how much you think is needed.

Using the above formula will let you dynamically add/delete project numbers to/from that list. The only restriction is to keep that list without gaps (empty cells - if you want to delete a number from the list is better to delete the entire row),

After defining the name, in every weekly sheet you have to enforce a Data Validation rule for the column where the project number has to be typed in. Select the column, go to Data -> Data Validation... and in that dialogue select List for "Allow" and for "Source" type in "=projects_no" (without quotes). If that column has a header you have to remove the Data Validation from the header, of course. Now the user will have to chose the number of the projects instead of typing it. This also prevents typing errors for those numbers.

Now in your Total sheet on the Project Number column you have to use a formula to refer the numbers in the projects_no name (range). Depending on the row where the list of projects number starts in the Total sheet, the formula will look like this (assuming list is in column A):

=IF(LEN(INDEX(projects_no,ROW(B2)-1))=0,"",INDEX(projects_no,ROW(B2)-1))

The above formula is for the first item in the project numbers list in A2 (presuming there is a header for that column in A1), but if your list starts in A3, for example, the "ROW(B2)-1" portion of the formula has to be replace with "ROW(B3)-2" in order to get a 1 value. Copy down that formula into as many cells you think the project numbers will be for all 52 weekly worksheets (that "100 rule" explained above for defining the dynamic range is also applicable here).

 

[gecs]

Sorry, the last formula in the above post:

=IF(LEN(INDEX(projects_no,ROW(B2)-1))=0,"",INDEX(projects_no,ROW(B2)-1))

should be replaced with this one:

=IF(ISERR(INDEX(projects_no,ROW(B2)-1)),"",INDEX(projects_no,ROW(B2)-1))

The rest of the explanation related to the formula is still valid, but using the LEN function was wrong because the dynamic range will have as many elements as non-empty cells are to be found on column A in the "Projects" sheet - referring an element with an index larger that the number of cells in this range will result in a "#REF!" error, so ISERR should be used to prevent the appearance of this error.

 

[krazyderek]

i think i follow you on most of that, someone setup a similar sheet for use at a race using data validation list's to keep track of all the racer information etc.

I can't seem to get the last part working though, neither of the LEN or ISERR formula's are doing anything on my end, i paste them into the cell and press enter but nothing happens, the cell just shows =IF(.... ?

Also i keep getting a warning triangle blocking the drop down telling me the project numbers are formatted as text instead of numbers cause we like to have the 0 in front of 08XX and 09XX project numbers, i just press ignore but can i supress that?

 

[gecs]

If you want to type in a value like "0923" and be displayed without being interpreted as number and shown as "923" you need to prefix it with an apostrophe: '0923. This will instruct Excel that the data should be interpreted as text. Like that you can type 'Jan-02 or '=A1 and the displayed value will be the same but without the apostrophe.

Regarding the use of that formula please check first if you have adapted it to your situation (the name, the index etc.). The IF part of that formula is used to display an empty string instead of the error when you copy the formula down and exceed the number of elements in the range associated to that name. The dynamic range definition is used when you need to modify the length of the range associated to a name and you don't know from the beginning how many elements will be in that range. Also when you need to reference that range (name) somewhere else you don't know how many elements will be retrieved, so you need to prevent the appearance of any error that might influence the behaviour of other related formulas too.

 

[krazyderek]

That's great, i don't know what i was doing wrong before but i made a sample sheet identical to your example and everything seems to be working so i'll sharpen my pencil a bit more. Thanks for all your help!

 

[krazyderek]

So, the new timesheet was a big success once everyone discovered the totals sheet, but the requests have grown.

I liked the idea of a drop down menu, is there a way to have a drop down menu for the project number, is there a way add name to be displayed in the cell after each entered?

I don't know if we're getting to much into a database style spread sheet, but if we could also put a billable y/n type cell after each project number that could be referenced for the type of project it is, ie: billable or not.

 

[thinpin]

Let me start by saying I know this thread is very old! But it has been extremely useful for me in putting together costs against our timesheets. Using the formulas above we have been able to put together a monthly spreadsheet showing costs against expended hours. We have then constructed a yearly spreadsheet showing total costs expended for the year My question is: currently, the only way to use this spreadsheet is by having all the timesheets open as well as the monthly cost spreadsheets which makes a total of 24 spreadsheets!! Each cell shows as a #REF error unless all the linked spreadsheets are open.

Please help!