As an instructional designer, I often need to estimate the time it will take to complete a project. One tool I use for determining my estimates is records of how long past projects have taken. Having everything together in a spreadsheet like this time tracking template also simplifies my invoicing to clients.
In addition, I track non-billable time. I want to see how long I spend on administrative tasks like invoicing, as well as networking and working with prospects. Knowing how long I spend on tasks like writing proposals helps me identify places I could streamline my processes.
I use a Google spreadsheet for my time tracking template. You can view and save a copy of the template yourself. You can also download the template for Excel.
Automated formulas save time
I’ve been using versions of this spreadsheet for at least 10 years. In that time, I have tweaked the template and added a few formulas to make the process faster.
- Time Spent subtracts the start time from the end time after you enter both.
- Rounded rounds time spent to the nearest quarter hour. I never invoice in increments of less than 15 minutes.
- Billable is a Y/N column. Administrative, business development, and professional development tasks are automatically marked N.
- Invoiced automatically changes to N/A for non-billable tasks.
- Use Ctrl+; to add the current date quickly.
- Use Ctrl+: to add the current time for Start Time and End Time.
- Leave the Invoiced column blank until you have actually sent the invoice. When you’re ready to invoice, filter for “blank” in the Invoiced column. That shows you everything you haven’t invoiced yet. Filter by project to see just the hours for that project.
Analyze with a pivot table
I use a pivot table to analyze how I spend my time. The time tracking template is set up to group data by month for invoicing purposes. You can also group by quarter.
You can adjust the pivot table to group by phases instead of month. This is especially useful when you’re estimating time for future projects. You can see how long each phase took for similar past projects and use that as the basis for your estimate.
You can also filter the pivot table by billable tasks, client, phase, etc.
Make it work for you
By default, the template is set up with ADDIE phases plus administrative, project management, professional development, and business development tasks. You can edit this list of Phases on the Named ranges tab. If you do change it, you also need to adjust the data validation in the Phases column. (The numbers in front of the ADDIE phases make them sort properly in the pivot table.)
Feel free to use this time tracking template yourself. Edit it to make it compatible with your workflow and needs. If you have questions or suggestions for improvement, ask them in the comments (or reply to this message if you’re reading this in email). You can also read more about how I determine my time estimates for designing and developing e-learning.
Originally published September 13, 2013. Last updated July 25, 2019.