Project Time Sheet

Post image for Project Time Sheet

by Steve Deal on November 28, 2012

This week’s fun request: a simple time sheet that can be used to allocate project time cost to different users. For example, if six users share an administrative assistant, he can track time spent for each of the ‘bosses’!

In Excel, it’s pretty straightforward – date / project name / how many minutes are to be allocated to each user:

Project Timesheet

Ah, but then the accounting people want to know, who pays how much?

In this case we wanted weekly totals.   As you can see above, I started by adding a column for the week.  This just uses an Excel function (WEEKNUM) to calculate the week number in the year from the date.   So WEEKNUM (“02/02/12”) is 5, since that’s in the fifth week of the 2012 year.

Next I created a second ‘Totals’ page, with a line for each week.  The ‘magic’ on this page is a cool Excel function (SUMIF) that is a conditional total –  something like “Add up all the boxes in the ‘Bill’ column that have Week Number ‘1’ “.

Add in a percent column and more totals, and it looks like this:

Timesheet Totals

Summary Totals

Here’s the spreadsheet – try it out yourself:  Timesheet.xlsx

Excel is a great ‘power tool’.  It’s easy to start using for simple lists & sums, but also has some ‘ooomph’ when you want to do something a little nicer.

{ 0 comments… add one now }

Leave a Comment

Previous post:

Next post: