Tracking Project time in Google Docs

I had a recent need to find a new method of doing time reporting against projects and services.  My services have different resource commitments, and its important that we show our customers and stakeholders that we are fairly delivering value.  Before you make the normal sigh, and under your breath mumble what a waste of time... hear me out!

Why does it add value?  Let's go to the root of the reason why we track time.  Is it to hold our staff accountable for punching a clock?  No, if you evaluate technology professionals on how much time they spend in front of a keyboard typing, shame on you.  Your skills are dated.  You should judge a person's contribution based on the value they deliver.  Would you pay a mechanic that looks busy for four hours $600 for a basic car inspection when everything passes and no repairs are necessary?  No, obviously not.  You want some type of value.  Judge your information technology by the same litmus.  What do I mean?  I honestly prefer my staff to spend the same time researching a problem and resolving it with a few lines of code that resolves the root issue... than develop a thousand lines of code that fixes the problem by masking the error.  The extra code just adds future maintenance costs.

Ok, I'm digressing... let's get back to the point...what is the purpose, really?

1. Tracking time against project and being OBJECTIVE can provide the data you need to improve your project management skills.  Are there areas of your projects where you consistently are optimistic and end up behind schedule?  
2. Could it be you need to add a weighting factor... since everything never goes well... and everyone plans for the most optimistic plan... or the most pessimistic plan... instead of the most realistic plan? You learn that factor from looking at your projects, and how well you've met the time schedules.
3. Do you have one resource that consistently can't meet the time schedules, when others continually are on-time?  You may have a training issue that you need to address... or you could have someone who is getting all of the more difficult work?  You only know by looking at results.
4. What is your actual non-project time?  You build a schedule based on programming time... how much additional time is spent in meetings, on phone calls, walking through unexpected use cases, out sick, training, vacation, etc.?  Tracking helps you figure this out.  I reallocate non-project hours against project hours to fully absorb these so I can tell my customers how much time was spent on each of their services.  This technique comes from my manufacturing background.
5. Don't be an idiot.  If you track time at an extremely high level to keep things simple, and can't tell other than looking at remaining hours where your project is at... you're not planning correctly.  If you can't identify a critical path across the project... other than "TASK A"... you could use some training.  I've blogged about this before... check it out.

Now, the solution for a small team.  I'm not sure how well this scales, so keep this in mind...

I've established a Google Doc.  The first parts' pretty simple... I have one week for each person to enter their time against project.  Now, for a little more advanced techniques... I want to be able to summarize by service, project, and task.  Since I have a smaller team (less than 10), I often have people working together on a project or service.  I need all of the summation to be correct and consistent, and it's unfair to ask everyone to type in the service, project, and task names identically.  So I've created a list of projects that my staff and I can maintain as a lookup.  Also, as a quick trick - you'll see red cells on the sheet that say "insert between here".  This is so total areas grow automatically to include new data when things are between the red lines.  You'll see what I mean... my formulas don't go from the first to the last number that should be added... they go to the red cells instead (Check out Fannie's tab in the Google Doc example below).

Here's a few more tricks for you:

1. Data validation - you can create a tab that has a list of valid choices so that all of your projects must be from the master list.  Check out Fannie's tab for the projects she reported time against... or try it yourself using the menu selection Data -> Validation.
2. VMERGE - what a cool llittle script - VMERGE allows you to get pull from multiple sheets, and put it in a single tab so you can analyze it.  You activate the script by going to Insert -> Scripts... and then type in VMerge.  You can also check out the linked documentation.  For a larger team, you may want to investigate the importrange function to see how well it scales.
3. Query function.  I've used SELECT, SUM, and GROUP-BY through-out the spreadsheet.  It's very SQL-Like - I opted to use column letters so as my sheets grow, I don't need to redo the formulas.
4. Where-ever it makes sense, Google Docs appears to allow columns instead of cell ranges.  This adds flexibility.  Check it out for yourself.
5. PivotTables - they are a great way to portray information as long as they can be predicted or controlled (rows vs. columns).  In my sheet, I opted for the most part to stay away from these... since my projects were limited and I wanted more granular control of my summary data.
6. Conditional formatting - Right click on selected cells - and select conditional formatting.  In my case, under 40 hours has a red background... and over forty has a green background.   Again.. check out the formulas within the totals on Fannie's page.

So, what does it look like?

1. Employee entry: 
   
   
   
2. Employee summary:
3. 

Summary report:

I've posted the generic spreadsheet so you can check it out... https://docs.google.com/spreadsheet/ccc?key=0Ag8WlpBJykv0dHdOZF9zN1FtelJLWTlaN1FKalNDd2c&usp=sharing

I'll cross post this on my excel blog.  Also, if you find you have some trouble with your projects, and you want to learn more... check out my prior posts.  Sure, there's some dry humor... what do you expect?  I usually write this stuff late at night when I can't sleep!!!

If you have specific questions, leave a comment, and I'll get back to you!

Thanks!