MS Excel Time Tracking - Calculating Billable Hours
In Excel you can set up a spreadsheet that will track the time you spend on multiple projects or on work for multiple clients to calculate billable hours. The trick is to convert Excel time tracking into a stopwatch.
Steps for Creating the Excel Time Tracking Spreadsheet
1. Begin by naming the column headers: Project/Client Name, Description, Start Time, Finish Time, Billable Hours and Total.
2. Fill in the project or name of the client in Column A and the description of the type of work done in Column B.
3. Use Start Time and Finish Time (Columns C and D) as your personal stopwatch with these shortcuts.
- When you are ready to begin working on the project, insert the current date and time into the cell for the Start Time by pressing CTRL+; (semi-colon), then press SPACE, and then press CTRL+SHIFT+; (semi-colon). When you are finish or want to take a break, repeat these steps for the finish time.
- Make sure that the columns for the Start Time and Finish Time are set on the date format Day, Month, Year 00:00 AM/FM.
4. Set up a formula to calculate billable hours in Column E.
- In Column E, you will subtract the Finish Time from the Start Time by referencing the previous cells, e.g., D2-C2.
- Make sure Column E is set on the time format 00:00.
5. Enter your hourly rate of pay in column F.
6. Set up a formula to calculate your total pay for each work activity in Column G.
- For each cell in Column G, you will insert the formula Billable Hours x Pay Rate x 24. For example, the formula for G2 would be "=E2*F2*24".
- Set the accounting format for Column G and extend it to two decimal places and then add a currency symbol by clicking the symbol located on the Ribbon.
7. To calculate your final total for each client or project, designate cells at the end of the spreadsheet and use a SUMIF formula with the condition being the name of the client. For example, for Client 1 the total pay is calculated with the formula "=SUMIF(A2:A8, "Client 1", G2:G8)".
Here is an example of the completed spreadsheet which shows the billable hours for a high school math tutor with three different clients.
You can create a separate invoice for each lesson by using Word's mail merge feature, an MS invoice template, and data from your Excel spreadsheet. Here is what the MS invoice template with the merged fields would look like.