A Flexible Auto-calculating Timesheet in 0.25HR Increments
The basic idea is pretty simple: use Excel as a piece of magic graph paper.
For my freelance time-tracking, I have a bunch of jobcodes that I use to tag the work I’m doing for a particular project. Unlike most job tracking spreadsheets, there is a single line to enter the starting/ending time for a task, which looks a lot better and is faster to edit. Using 2400 time, the spreadsheet uses tricky formulas to make calculating time easy in 0.25 hour increments for billing purposes with a line like 1215 research data structure in use 1415, which translates to “from 12:15PM to 2:15PM, I researched data structure for the client”.
Since it’s just an Excel spreadsheet, you can attach whatever additional information you need, which makes it a nice one-stop reference for all my time accounting. I make a new one every year, and archive the old one.
Currently, I use three kinds of worksheets in my Time Tracker workbook, but you can do anything that makes sense for your job of course!
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.
The Particulars
My favorite part of the system is the time entry. I hate clicking on a lot of different fields. A single-line entry format makes each task self-contained, and the special formula calculates the number of hours in my 0.25 hour increments.
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:
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 for tracking time with Excel. You can also read the original 2005 post Doing Time in Excel which describes its origins!
Enjoy!
