Software

Create a timesheet in Excel to track billable hours for your customers

Excel lets you vary the calculations you perform on specified cells using a single formula. Here's a look at how the technique works and one good example of when you might want to use it.

You can use an Excel spreadsheet to keep track of your billable hours: Just list the start time in one column, the end time in a second column and then subtract the first from the second. But what if you don't charge the same for each block of time? For example, suppose you're working on three projects for one customer. You want to charge the hours directly spent on the projects in full, but you want to charge the customer for only half the time spent administering the project. Follow these steps:

  1. Enter the labels for the worksheet as shown in Figure A.

Figure A

labels

  1. Select F2:G9. Right-click the selection and then select Format Cells.
  2. Under Category, select Time. Under Type, select 1:30 PM (Figure B). Click OK.

Figure B

time

  1. Select H2:H9. Right-click the selection and then select Format Cells.
  2. Under Category, select Custom. Under Type, select h:mm  (Figure C). Click OK.

Figure C

Custom

  1. Right-click H10 and then select Format Cells.
  2. Under Category, select Number (Figure D) and click OK.

Figure D

Number

  1. Select H2:H7 and then enter the following function (Figure E):

=IF(OR(E2="Project 1",E2="Project 2",E2="Project 3"),G2-F2,(G2-F2)*0.5)

Figure E

function

  1. Press Ctrl + Enter.
  2. Enter the data as shown in cells D2:G7 (Figure F).

Figure F

results

Excel automatically calculates half the time for Admin and full time for the projects. The total number of hours is calculated in H10.


Miss an Excel tip?

Check out the Microsoft Excel archive and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

7 comments
CharlieSpencer
CharlieSpencer

First line - start at 8, end at 11, worked 1:30? Fourth line - start at 8, end at 12:30, worked 2:15?

MC68000
MC68000

...something's missing. The hours worked do not total in H10. Also, the url for the backtrack doesn't open a page.

ThumbsUp2
ThumbsUp2

They're only tracking 1/2 of the Admin hours.

cara1211
cara1211

....the hours worked for several of the entries aren't correct in the totals column.

rkuhn040172
rkuhn040172

Wouldn't it be better and more realistic to track actual hours and charge 1/2 the rate? I would think someone would be interested in total hours used even if they are charged a different rate.

n2iph
n2iph

The total and the sub totals all add up as they should when you apply the formula. Remember that when Admin is entered in column E the calculated hours are halved.

Editor's Picks