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:
- Enter the labels for the worksheet as shown in Figure A.
Figure A
- Select F2:G9. Right-click the selection and then select Format Cells.
- Under Category, select Time. Under Type, select 1:30 PM (Figure B). Click OK.
Figure B
- Select H2:H9. Right-click the selection and then select Format Cells.
- Under Category, select Custom. Under Type, select h:mm (Figure C). Click OK.
Figure C
- Right-click H10 and then select Format Cells.
- Under Category, select Number (Figure D) and click OK.
Figure D
- 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
- Press Ctrl + Enter.
- 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.
