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

  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

  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

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

Figure D

  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

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

Figure F

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.

Subscribe to the Developer Insider Newsletter

From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays

Subscribe to the Developer Insider Newsletter

From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays