Time Tracking in Excel

Time Tracking in Excel

"Timesheet Screenshot" For my freelance time-tracking, I use a jobcode-based Microsoft Excel spreadsheet. It’s got a few tricky formulas to make calculating time easy. When I first put this together in 2000, I used Excel’s sorting feature to give me sense of “how much time” it took for specific classes of task. While I keep thinking of switching to a real accounting system, I still use this one to this day.

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 in 15-minute increments, and fractional hours are calculate automatically. 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 spreadsheet 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 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 something like this:

  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.

Sorted TimesheetWhen 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.

Finished Sub Timesheet 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 for tracking time with Excel.