Pain-Free Way to Add Up Billable Hours

Juggling multiple matters means adding up many time increments to come up with the correct number of billable hours for each matter, each day.  Here’s an easy way to add up all the time on a given matter automatically.

Set up a simple table where you’ll enter the begin time and end time for each increment of billable time:

timesheet - table

Format the times as follows:

timesheet - format time 2

Use the following formula to calculate the length of time, in minutes, of each chunk of time spent on the matter:

timesheet -formula

Excel defaults to a fraction of a day when it performs this calculation–the formula multiplies by 1440 to turn that into minutes.

Format the cell calculating the duration as follows to make sure it displays correctly:

timesheet -format difference 2

Now copy the formula down for all the rows in your table (one way to do this is to grab the cross-hairs at the lower right-hand corner of cell D4 (the cell with the formula) and dragging it down):

timesheet -copyformula

Next, use the following formula to calculate the total time billed to the matter that day.  The formula adds up all the time increments and divides by 60 to generate the number of hours:

timesheet -total

This formula adds up all of the time increments, and then divides by 60 to return the total as a number of hours.

You can use the “Increase Decimal” button to display the total in tenths of an hour:

timesheet -increase decimal

Set up a table like this for each active matter, and you’ll be done computing your time in no time at all.

How do you keep track of your billable hours? Please share by leaving a comment below.

Follow the Excel Esquire blog to get more time-saving tips, and to learn about CLE-accredited Excel workshops.  Thanks for reading!

Advertisements

About excelesquire

NYC attorney and Excel enthusiast.
This entry was posted in Everybody, formulas, Practice Management and tagged , . Bookmark the permalink.

3 Responses to Pain-Free Way to Add Up Billable Hours

  1. I use a stopwatch timer on my phone and then enter the different chunks into my spreadsheet that has columns for each part of my responsibilities. Each month is on a tab, each work day on a row totaling on the right. I use conditional formatting to highlight the current day’s row based on a formula using today(). That way I never type into the wrong row 🙂 Formula for conditional formatting where column A contains work dates. Formula: =$A3=Today() Applies to: =$C$3:$K$23 (Format with a light yellow fill). Hope this helps someone else 🙂 SlinkyRN

    Like

  2. Andrew S says:

    So, I work in accounting, which is a little different than law, but one of my staff developed a similar system.

    There are a few differences that may or may not be applicable to you: for example, she put it so that the “start time” for the next line automatically populates with the end time of the previous line (so the only time you have to manually change that is if you took a break or you’re starting a new day — this is especially helpful if you’re juggling between clients, administrative tasks, etc., but you’re pretty packed.)

    We also include lines for engagement name, activity code, etc., in the same row (rather than as the header for the workbook, as is done here), so that we can be as continuous as possible. Instead of summing at the bottom of the workpaper, we create a pivot table that looks like our time reporting page for the week. This shows all the engagements and activities with a sum of hours per day for the week.

    The hiccups are that the pivot table has to be refreshed for each week (but I would have to pick the week to show anyway, so that’s not a big deal), and new engagements don’t automatically get selected in the refreshed pivot (although that’s not a big deal). And I ideally would like to have some sort of macro where I can just click a start and stop button and have a new line populate with the correct day, time, etc., but it’s not too difficult to manually put these things in. (Really, I ideally would like it if the firm let us import into the time reporting system, but that will probably never happen!)

    Like

  3. Calio says:

    This helped me a lot, thanks!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s