(last edited on October 12, 2023 at 4:58 pm)
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
25 Comments
This looks interesting, I’m going to give it a try. I’ve also been testing out sidejobtrack.com. You might want to give that a look, it’s free and hosted, and you can use it for estimates, invoicing, and reporting. I guess that’s what steers me away from something like Blinksale, even though it’s great, I don’t want to log in to a bunch of different apps to manage clients, I want to do it all in one place. I’ve been looking for something sort of like this that I can put on my own host. Maybe I’ll just have to build it myself.
This is exactly what I’m looking for. The only thing I can think of is the use of Basecamp.
Has anyone tried this?
Update: Okay, I re-read Beth’s comment above about SideJobTrack and it looks VERY GOOD as well! Thanks for that tip! I’ve never heard of it.
OK, I’ve been using this for a couple of weeks and, once you get your head around it, it’s pretty useful. However I’m really digging sidejobtrack.com… great stuff all round.
Thanks for making this spreadsheet available though – it’s saved my ass once already.
I’m going to give sidejobtrack a whirl too…it looks neat. More later :-)
Well, Dave, did you ever check out Side Job? I’m really interested in getting your feedback, especially considering your user-interface background.
Hi Marie! I got as far as registering, but have been busy with work and the holiday and haven’t yet checked it out. Looking very much forward to it though! Hopefully I’ll get some time next week.
I’ve been playing with your worksheet and it’s given me a lot to think about.
I’m not a big excel user, and in fact have been working with the spreadsheet in gnumeric on a freebsd system.
There are a couple of things that I don’t understand:
– why do you use the funny integers for dates, instead of simple date strings?
– why did you go to the trouble of putting the starting and ending times at the front and back of the description column instead of giving them their own column?
Thanks for sharing the worksheet, it’s been very interesting.
g.
Hi George:
Dates: In Excel they’re shown as date strings, though they probably actually are stored as integers that are being displayed as-is in Gnumeric (I’m not familiar with it
Timestamp: I hate tabbing back and forth between fields when doing data entry. Invariably I over-tab, or under-tab, and accidentally erase the time. This way, I can enter in a single line and edit it just by clicking F2 and using the HOME/END keys to access the time. You could certainly split the times out into separate fields…I just like it better this way.
Thanks for the comment!
Dave
Check this out: http://www.formassembly.com/time-tracker/#
Hey Scott, thanks for the heads up! Looks like an interesting web app. I’m thinking of a time-keeping application that is more like a paint program…it could be really cool
http://www.88miles.net takes a different approach which can be much less time consuming…
When auto entering a date next to a cell that has been read in via barcode reader, how is this “timestamp” kept as the actual time/date?
Right now it updates everytime I reopen the fil.
Thanks for any help!
Tim: does this have anything to do with this spreadsheet?
I don’t have a barcode reader that autoenters anything, so I am going to guess a macro that’s returning a NOW() or TODAY() function, as opposed to a static date (CTRL+; or CTRL+SHIFT+;)
I have been enjoying the clock in and out features of http://www.getharvest.com/
I don’t have to bill clients or have many projects at a time so the free version has been stupendous.
Nice hack on Excel Dave! Who needs QuickBooks. Thanks for the info
Hi,
I have one problem hope you can solve that.
I want get the time automatically in other worksheet while entering any digit or alphabate in to the another cell. In this 2 worksheet will be there. Help me if you have any solution for this.
Have you checked out SlimTimer? It’s a cool little javascript-packed webpage that you open with a favelet. Very slick…
Hi … Similar to the above time keeping module… Can anyone help me with the VBA macro coding for capturong system login as well as logout time.. so that the time can be captured on the cell as a value…
URGENT plz mail me to wsminkapila@gmail.com
Thnxx
Excel Questioners: Sorry guys, I’m not an Excel Whiz when it comes to macros and stuff like that.
Eric: I did check out SlimTimer, very cool.
Thanks for the spreadsheet. Nice work.
Thanks, Dave – great resources!
I use Process Dashboard (http://processdash.sourceforge.net/) for time tracking.
It’s probably overkill for simple time tracking, but, if you do software development it comes with lots of additional things that are interesting…
Hi Dave,
I was wondering if you could help me, i have 2 times, 1 start and 1 end, now all i want is a formula to work out the hours and minutes from the start time to the end time. ie: 08:00 am to 14:00 pm gives you 6 Hours 0 Mins.
Any help would be great.
Thanks
i created an excel timesheet recently and have given to my colleges to work which gets sent to me every Friday.
It works with checkboxes and each checkbox = 30 min. It has 6 days of the week and 10 hours in a day 6 optional projects per day. You fill in Job Code, brief job description and then it is just clicking on the checkboxes – it automatically works out daily hours spent and weekly. Could easily add monthly. (If you want to take a look let me know.)