Doing Time in Excel

I was chatting with some friends who were just getting started in freelancing, and the topic of time tracking came up. For my freelance time-tracking, I'm still using a cobbled-together system based on Microsoft Excel. I first put it together in 2000, when I wanted to really get a sense of how much time I was taking for specific tasks; it's now the basis of my daily project workflow. I am planning to switch to QuickBooks so I can integrate time tracking, invoicing, and (most importantly) reporting in a single package. However, I am still fond of the old system; if you're running just a few jobs at a time, you might find it useful.

The basic idea is pretty simple: use Excel as a piece of magic graph paper, with individual worksheets fulfilling different functions. There are three kinds of worksheets in my Time Tracker workbook:

• A scrolling Time Sheet, used to record time spent in each project in quarter-hour increments. The time entry system uses 2400 time, and automatically calculates hours from it. The spreadsheet is also designed to be easily sorted so I can extract per-project hour information. I start from January and close it at December. And, it also works as a pretty decent day-to-day To-Do List that allows for slippage. I start a new one every year.

• A client information notepad, used to record contact information about clients like assigned passwords, database info for my various websites, FAX numbers, and other information that's useful to have in the day-to-day. The reason why it's in this workbook in the first place is that I try to put as much related information into a single place as possible; I have this file open all day, and it's just easier to know that I can look things up in this file. The entire file is password protected to keep prying eyes from casually inspecting the file.

• Finally, there are various hour invoicing worksheets, one for each job. Because of the way the Time Sheet is set up, it's relatively easy (but not automatic unfortunately) to extract the hours and keep them here as a running total month-by-month.

My favorite part of the system is the time entry. I hate clicking on a lot of controls, so I devised a single-line entry system that combines the item description with the time information. A special formula calculates the number of hours, which can then be summed and manipulated.

Column D of the Main Log is the time/description field. You enter in your time items like this:

    0930 Read architecture document 1015
    1015 Create first pass design 1045
    1045 Package and submit 1100
    1100 Create second pass design 1500
    ...

You'll notice that the first four characters of the field is a number: this is the time you started the task, in 24-hour time. At the end of the line is the time you ended the task, also in 24-hour time.

Column C converts from 24-hour time to duration in hours, using the first four characters and the last four characters in Column D. That's the cool part. You never type in this value. Here's what you do:

• Throughout the day, you keep entering what you're doing, to the closest 15-minute increment, in Column D. That ensures that the hour duration values are in increments of 0.25 hours, which is easier to deal with.

• At the end of the day, copy an existing Column C cell into the empty cells next to your Column D items. All the hour durations will be automatically calculated. You can even adjust the times later item-by-item, and the duration will automatically recalculate.

Column A is the date. The first line of each day just contains the date as a separator, with each following day also prefaced by the date. The aesthetics of this make it easy to visually separate each day, while the color coding makes it easy to distinguish weeks from each other. I pick colors for the week based on my mood :-) You should have the date on each line for when we do sorting later.

Column B is the job code. I number them from 0100 and up (numbers less than 0100 are old projects from before I started numbering them). Every piece of billable work I do is assigned to a jobcode. The jobcodes are a central organizing principle also for my Project folders and Invoicing. It just makes it easier to associate a particular project directory with a particular piece of billable work. The directory structure looks like this:

    D:_local_projects
        0013 Project 1
        0014 Project 2
        0015 Project 3
        ...

You get the idea. Each folder contains the actual source files, drafts, documentation, and deliverables I work with. Ideally each folder will allow you to recreate the project from scratch as needed, but that's a subject for another day.

Anyway, I keep both private and public projects in my projects folder, assigning a new project number whenever I'm doing something that can be considered "new and separate" from another project. If a separate contract is involved, that's a good time to assign a new project code. When I'm doing a time-intensive Request for Proposal (RFP), I will also assign a job code and start tracking the hours spend in case there's a change they could be re-imbursed, or I suspect I might need some leverage...so far, I've been lucky. Job codes are good for maintaining continuity.

When I'm ready to actually sum-up all the hours to invoice a job, I copy the hours into the related hour invoicing worksheet. Use the "Copy Worksheet" to create a copy of the main timesheet, then extract the date entries for the period you want. You could also just copy a range of rows into an existing worksheet. The image shows the result of the sorting operation, using columns B, then A, then D. You can see that the display has been sorted by job, then by day, then by time. I inserted the spaces for clarity.

After you've cleaned things up, you end up with this: a nicely-formatted summary of hours.

You can use Column E to tag individual entries for purposes of figuring out your overhead ratio: that is, the number of billable hours spent per non-billable hours (this is one factor you can use in determining your base rate after you've been working for a while). You could also tag different parts of the project as graphics, management, research and so forth, to determine what percentage of the projects was spent doing what task. Knowing this information can be really useful in providing estimates for future projects. For example, from tracking all this data I have a rough idea of how long it takes me to make one finished GUI design, how long it takes me to do 1 minute of Flash motion graphics at a certain level of complexity, and approximately how much time I spend communicating with clients as a ratio of production time.

So that's my system, such as it is, for tracking time with Excel. I had started to create an automated version of this in ASP at my old workplace for multiple users. I was learning ASP at the same time and never got around to completing it. I had a pretty awesome database schema too, as far as I could tel. The system could have theoretically tracked interesting performance metrics for week-by-week reporting: the prototype spreadsheet for that rocked, but was way too manually-intensive to run easily. But again, I digress...

Drop me a note if you find the TimeTracker workbook interesting. Share and Enjoy!

» Download TimeTracker_Example.zip
should work with recent versions of Microsoft Excel

Comments