Excel and Numbers Templates for Attorney Billing Timesheets
May 5, 2009 at 2:16 AM by EsquireMac 
I do a fair amount of billable hour work in my practice. Over the years, I've developed a fairly simple but flexible spreadsheet for tracking my billable time. For our firm, this represents the ideal solution at present. I have taken a liking to a few different Mac billing apps out there (like Billings, Involer, Invoice, GrandTotal, and iRatchet) but each of them falls short in one way or another for our purposes. I would encourage you to give them a try, however, as your needs might be different than ours.
Early on, I would write my time down here and there, and at the end of the month, I would be gathering my snippets of billing information from all over my office and my computer - even going back over my recent emails and documents to forensically reconstruct my billable time for the month. As you can imagine, you lose a fair amount of billable time that way - both for time it takes to recreate the month, and for the work that ultimately never gets billed for because it was missed in the reconstruction.
Obviously, I needed to find a system for capturing my time contemporaneously with the work I was billing for. For a while, my solution was to keep a legal pad next to my computer and jot down my work as I was doing it. That's not a terrible solution, but it left me at the end of the month with a good bit of work still to do. I would go over the pages of chicken scratch (yes, surprise, my handwriting is horrific), and try to give a client-by-client report to our secretary who creates and sends out our billing invoices.
Eventually, I realized that by using a spreadsheet, I could capture my time as I do the work, and, at the end of the month, we can manipulate the data in any way we want. Specifically, the data can be arranged by client or matter to give our billing secretary easy access to copy and paste the relevant information.
As most firms do, our firm bills by the tenth of an hour.
It is easy to type in the start time and end time for your tasks in the spreadsheet. The hard(er) part is to do math with the time, convert it to tenths and round it up to the next tenth. Of course, this can be done manually, which is how I used to do it, but the with power of Numbers and Excel, it can easily be done automatically - if you know the right formulas.
First, you need to make sure your start and end time columns are set to the Date and Time format. The next step is intuitive enough: subtract the start time from the end time. If that is all you do, however, your result will be something like "0.0" because the spreadsheet doesn't fully understand what you're up to. Multiplying the whole answer by 24 will give you a decimal representation of the time. But, you're not done yet. At this point, you may get an answer like 0.717. You could just set the decimal places to 1 in the cell format settings, but that would round to the nearest tenth, rather than rounding up. The solution is to add the ROUNDUP operator to the beginning of the formula. To tell the spreadsheet what decimal place to round up to, add a ",1" to the end of the formula.
In the end, your formula for calculating time to the tenth of an hour, rounding up, is "=ROUNDUP((B1-A1)*24,1)" where B1 is your end time and A1 is your start time.
So, without further ado, here are two templates for tracking your billable time:
One final tip: I have found that in order for me to practically capture my time as I do the work, I need to have the spreadsheet very quickly accessible. The way I accomplished this was to put the billing document in my dock. Just drag the document from where ever you keep it to the right side of the dock (where your "stacks" folders are kept), and it will stay in the dock for one click access to your timesheet.
Law Practice Management
Reader Comments (12)
Adam, thank you for the tips and the templates. In my solo practice we use timesolv (an online billing solution), but sometimes it's not flexible enough for court presentation of our fees. Your template will fill in those gaps for us.
Hi Esquiremac
I admire your spreadsheet. It is ingenious, quick and easy to use. However, I use RTG billing software because it does two things for me. First, I can start it and select a project to assign time to and then RTG gets out of the way, keeps track of time spent, and comes back when I want it. Second, it will take my time and convert it into billing documents, which step is still requred by an Excel program. Selecting the project is a littlle time consuming, but I don't have to rename it each time.
Thanks for sharing your efforts.
Regards,
John
I was doing exactly this before I moved over to a timekeeper solution. It would easily calculate my time from my start and end times, rounding up to the tenth of an hour. Worked pretty well too, but I got tired of adding up all my time at the end.
This is fantastic! I charge flat rate fees for most of my work, but occasionally need to keep track of billable hours. As a newly-solo attorney I haven't needed or wanted to pay the hefty price tags for all the software out there has to offer. This suits my needs perfectly for the time being. Thank you!
I'd like to recommend Time59 (http://www.time59.com" rel="nofollow">www.time59.com) for your billing. It is web-based, handles trust accounting and is very reasonably priced.
Yeah cheers for all the heads up, oh, and I can relate to you Isaac I use timesolv where I work - and it drives me silly sometimes - the flexibility is kind of annoying, not only that, but I have the UI.
Grr, anyway, thank you for the advice :)
Regards,
Jakk :D
Thanks Mac,
My field of work is entirely different but this template is awesome.
I've also tried a lot of different solutions and at the end spreadsheet seems like the best way to go for tracking these things.
On MacOS, we use TimeTracker. It stays unobtrusively in your system menubar; enabling you to change tasks/clients with just a few quick clicks. At the end of a billing cycle, we'll just export the data into a CSV, and let NeoOffice do the math for us.
I used this template briefly a few months ago for recording time. At that time, I did not want to have to cut and paste the data into an invoice. After looking at several time and billing apps—including actual demo trials of Billings 3, EasyTime, Freshbooks, and Harvest—I did not find anything that was an ideal match for use in my solo law practice. SaaSs are too expensive long term.
I am looking this template again. I have it stored in my Dropbox account, where it is readily available from my Dropbox computers. Using DropDAV with Dropbox, MobileMe with iDisk, or an iTunes sync, I can copy the template to and from my iPad for mobile use with some limitations.
Unfortunately, the Numbers iOS app is more functionally limited than the OS X program. On the iPad, Numbers works just fine with EsquireMac's basic template. Given the limitations of the iOS app, I'll see to what extent the template used to capture time then modified directly or after to make the data from template more useful in Numbers for OS X to avoid having to cut and paste.
That, or look at a Bento-type solution : )
I'll shortly be joining a new law firm and the timekeeping/billing software my firm uses is not Mac-compatible! So I thought I'd give your template a go. I'm afraid I'm a bit of an Excel newbie though, so if I use your handy template, how do you thereafter arrange the data by client or matter to give the billing secretary easy access to copy and paste the relevant information? How do you create new worksheets based on client/matter?
Thanks.
Using Excel I have noticed that thirty minute increments from these specific times of 8am, 11 am, noon, 1pm, 3pm, 4pm, 6pm, 7pm and 9pm yield .6 hours as opposed to .5 hours. I do not know why. However a slight modification of the formula corrects this anomaly. Try this:
=IF(A1="","",(ROUNDUP(((((B1-A1)*24)-0.01)),1)))
Where B1 is your end time and A1 is your start time. The “If” function (IF(A1="","",) merely allows me to omit any blanks in a field of a column.
Thank you! I don't know why this anomaly exists, but several people have reported rounding errors where, per the formula, there should be none. I haven't tried your fix (as I long ago moved from spreadsheets to keep time) but it seems to make sense.
Thanks again!