Create a timesheet template to help track your billable hours
—by Sean Kavanagh
Microsoft Excel 97/2000/2001/2002
Microsoft Windows, Macintosh
If you’re only required to adhere to the old punch-clock method of recording your time, your time-tracking responsibilities are pretty easy. However, some jobs have pickier requirements than just track-ing your in and out times for the day. Even if you don’t have to consistently do so, it’s almost inevitable that you’ll find yourself in a situation that requires detailed time tracking. If you work on several projects, you probably need to separate and categorize time spent on different work tasks. If your time is billed to clients by the hour, you also have to keep track of billable versus non-billable time. Since every penny counts, you may need to track your time down to the minute. Eventually, you may remember that seven minutes equals .12 hours; but trying to keep track of time in this manner can be a nightmare. Fortunately, you can use Excel to simplify this process. In this article, we’ll show you how to create a timesheet that calculates the time spent on individual tasks, like the one shown in Figure A.
You can use Excel to convert elapsed times into decimal values and simplify your time-tracking tasks.
Tracking time in Excel
Our sample worksheet, which is available for down-load from the URL listed at the beginning of this ar-ticle, will let you track your billable time and even break it down by the projects on which you’re work-ing. We’ll create a template that includes line items for each block of time corresponding to your daily activities. You’ll enter starting and ending times for each task and a project code to indicate what client or project the activity is associated with. Excel will then calculate the time you’ve worked in terms of hours. You’ll have a space to enter a description of your activity, and you’ll be able to indicate whether the work was billable or non-billable.
Create the basic timesheet form
The first step you’ll take in creating the spreadsheet is setting up the general format. First, open a new Excel workbook and enter the headings from Table A.
When you’ve finished entering the headings, select cell B1. While holding down the [Ctrl] key ([command] on the Mac), select cells C1, F1, and B3. From the Format menu, choose Cells. Next, click on the Border tab. Choose the second dotted line under None in the Style list box. Now, click on the bottom of the sample cell displayed in the Border panel so a dotted line appears. Finally, click OK.
Next, select cell A1. Again, while pressing the [Ctrl] key ([command] on the Mac), select cells A3 and E1. Press [Ctrl]B ([command]B on the Mac) to apply bold formatting.
At this point, select range A5:G35. From the Format menu, choose AutoFormat. Select the List 3 thumbnail and click OK. Excel applies the format you chose to the selected range.
Click on the column D heading and then click the Center button ICON. Next, click on the column G heading. Choose Format | Cells from the menu bar. Click on the Alignment tab and select Top from the Vertical dropdown list. Then, select the Wrap Text check box and click OK.
Now, select the row 5 heading and display the Format Cells dialog box again. Select Bottom from the Vertical dropdown list, select the Wrap Text check box, and click OK. Finally, resize the columns so your worksheet resembles the example shown in Figure B.
Reformat your worksheet so it resembles this figure.
Format the input cells
We’re almost ready to add the calculations that de-termine elapsed time. First, we’ll format the range that will store your starting and ending times. In cells B6 and C6, enter 0.
In most cases, you can assume that the time frames represented in each row of the timesheet will be consecutive, which means that the End Time in one row will most likely be the Start Time in the next row. There’s no reason you should have to retype that information—let Excel use the same value by default. To do this, select cell B7 and enter the formula =C6. Since the value in cell C6 is currently 0, that’s what’s returned in cell B7.
The formula you entered needs to be copied to the rest of the cells in column B. Select cell B7, then click on the fill handle in the lower-right corner of the cell and drag down to cell B34. Next, select cell C6 and double-click on the fill handle. A column of zeros is created next to the column you manually created.
Now, select range B6:C34. Display the Format Cells dialog box as you did before, and then click on the Number tab. You need to apply a time format to your entries. Select Time from the Category list box. Then, select 13:30 from the Type list box and click OK to produce the result shown in Figure C.
Apply a Time number format to the Start Time and End Time range.
Calculate elapsed time
Now we’re ready to enter the timesheet calculations. The first calculation will determine if time should be counted as Billable Hours. In cell E6, enter the formula:
which returns a value of 0.
Let’s take a closer look at this formula. First, it checks the value in cell D6 to see if the time being calculated is billable, as indicated by a Y entry. If the value in D6 doesn’t equal Y, 0 is returned. Otherwise, Excel subtracts the start time from the end time and multiplies the result by 24. Since Excel stores time as a fraction of a day, multiplying the result by 24 returns the time expressed as a decimal value in terms of hours. For example, 45 minutes, which is three-fourths of one hour, is expressed as .75. Three hours and fifteen minutes is represented as 3.25, and so on.
Once the decimal time value is calculated, it’s multiplied by the expression:
The reason for this is to eliminate odd results that can occur when the ending time hasn’t yet been filled in a line item. To demonstrate why it’s necessary, let’s consider how the formula would work without this expression.
For instance, let’s say that you were to fill the first Start Time cell with a value of 8:00. The fractional value that Excel equates to this time is .333 (with a repeating decimal value). Subtracting that value from the initial End Time value of zero creates a result of -.333. Subsequently multiplying that result by 24 produces a value of -8. Obviously, this would cause problems when you calculate your total time for the day. Although the problem would be solved on the current row when you enter your first End Time for the day, the next row would then exhibit the problem because the Start Time value automatically equals the previous row’s End Time value.
Rather than make the user clear the final extra Start Time, we prevent any result from appearing in the Billable Hours column if it’s less than zero, replacing the value with zero to avoid throwing off your time summaries. To do so, the expression ((C6-B6)>0) per-forms a logical test. If the result of C6-B6 is greater than 0, the result is TRUE, otherwise the result is FALSE.
Excel equates TRUE to a value of 1 and FALSE to a value of 0. Therefore, when you consider our previous example using our complete formula, it evaluates as follows:
((C6-B6)*24) * ((C6-B6)>0)
((0-8:00)*24) * ((0-8:00)>0)
((0-.333)*24) * ((0-.333)>0)
(-.333*24) * (-.333>0)
-8 * FALSE
-8 * 0
As you can see, we’ve ensured that the Billable Hours column will only contain positive values or zeros.
To calculate non-billable hours, select cell F6 and enter the formula: