Time Sheet Template

Most HR departments and small business owners have the challenge of tracking employee per task in order to allocate costs to tasks and process employee payroll. In this blog post we have created an easy to use, copiable, Time Sheet Template that allows you to track the hours of multiple employees over multiple proejcts, is ready to go after a short configuration, and is all built on Google Sheets for easy and convenient customization.

Overview of the template

This template includes two Google Sheets, one of which the HR manager inputs with collaborator names and tasks and the other of which is used by the collaborator to report their time. The collaborator sheet can be replicated multiple times (e.g., one for each employee). We’ll configure the Manager sheet to import project assignments to the Collaborators sheet(s) and the Collaborator sheet(s) will be filled in daily and imported to the Manager sheet to report hours worked.

Before we start

As a best practice input fields are white and all other fields are colored. The sheets are configured to accept 22 employees and 100 unique tasks. These limits are easily increased with a few simple changes the default formulas.

Follow the steps below to configure your Time Sheet Template:

Step 1) Install Import Sheet and Copy the Template

If you haven’t done so yet, install the Import Sheet add-on by clicking this link: Import Sheet Add-on for Google Sheets. Next click on the icon below to access the two sheets needed for this template, Manager Sheet, and Collaborator A Sheet. Open the sheets and make a copy of them by clicking ‘File > Make a copy…’

Inventory Management Template: Install Import Sheet and Copy from the Template

Time Sheet Template: Install Import Sheet and Copy from the Template

Step 2) Fill in your projects and tasks

Open the Manager Sheet and fill out the tabs ‘Projects’ and ‘Tasks’. In the ‘Projects’ tab fill out columns B, C, and D with the projects, project descriptions, and estimated hours. In the ‘Tasks’ tab fill out the name of your employees in row 2 and, in column B fill in the project name with the pull down list, and finally in column C give a unique name for individual project tasks. To assign a collaborator to a task place an ‘X’ in the cell that corresponds to the collaborator and task as shown below:

Time Sheet Template: Assign collaborators to tasks

Step 3) Configure the Collaborator Timesheet sheet

Configure Collaborator Timesheet to import task assignments from Manager Sheet. Before connecting we recommend renaming this sheet to the name of ‘Collaborator A’ (e.g., ‘John Smith Timesheet’)

  1. Click “Add-ons -> Import Sheet -> Start”, this will open the Import Sheet. Click on the + in the lower right corner to create an Import connection.
  2. In the next screen, select the Connect as type of connection
  3. Find and select your copy of the Manager Sheet spreadsheet. spreadsheet. In section “Select source sheet” select the sheet “Tasks“. In section “Automatic Update” click on “edit” and change the first field to “Daily”, the second to “Midnight to 1AM” and click “OK“. And in section “Connection Name” click on “edit” and type “Tasks” (it must be exactly this name). Take a look at the image below for an example of how your options should be configured and then click “CONNECT“:

After importing the tasks select the collaborator name from on the configuration tab. You can copy this sheet to your other team members, however you will have to recreate the connection between Manager Sheet and Collaborator X Sheet. You can now give your collaborators access to their sheets to fill in their hours daily on the ‘Project Timesheet’ tab.

Step 4) Consolidate the Collaborator Sheets in the Manager Sheet

We will now configure the connection between your ‘Collaborator Sheets’ and ‘Manager Sheet’ so that employee times are automatically reported to the manager.

  1. Click “Add-ons -> Import Sheet -> Start”, this will open the Import Sheet. Click on the + in the lower right corner to create an Import connection.
  2. In the next screen, select the Consolidate as type of connection
  3. Click on [+] button to add the first collaborator spreadsheet, find and select your first ‘Collaborator Sheet spreadsheet. In section “Select source sheet” select the sheet “Project Timesheet“. Click on [+] button to add more spreadsheet in this consolidation, now you will search for your second collaborator and select their ‘Project Timesheet’. Repeat this step until you have selected all of your collaborators. 
  4. When all spreadsheets are already added, click on “OPTIONS” in section “Automatic Update” click on “edit” and change the first field to “Daily”, the second to “1AM to 2AM” and click “OK“. And in section “Connection Name” click on “edit” and type “Collaborators Timesheet” (it must be exactly this name) Take a look at the image below for an example of how your options should be configured and then click “CONNECT“:

Congrats!

You’ve now set up your Time Sheet Template which allows you to assign projects to collaborators and allows collaborators to report their hours daily to you. The system is now yours. Feel free to customize it as you see necessary.

Comments are closed.