Log Time Using Google Forms and Create a Timesheet Report in Google Sheets
Google Forms is an easy to use tool to collect information for surveys, events, and pretty much anything else. It's easy to use interface allows you to build forms quickly and view the response data directly in Forms or in Sheets.
In this guide, we'll create a timesheet form where an employee can track their time worked in Google Forms and have the information saved in a Google Sheet. Then we can build a report to view the timesheet by day, week, or month and by employee.
Google Forms Timesheet
First, we'll create a Timesheet in Google Forms. The form will allow an employee to select his or her name, the date, the time-in, the time-out, and any break time which we'll record in duration (hours/minutes).
You can change the time settings from time to duration by clicking the options button next to the required indicator.
Now that we have all of the fields created, we can link the responses to a Google Sheet by clicking on Responses and then the Create Spreadsheet icon.
You will then be prompted to create a new spreadsheet or select an existing one. We'll create a new spreadsheet and leave the name as "Timesheet (Response)".
Our form is now ready for use. You can distribute the form via email, share the link, or embed it within a website. Here is a copy of the Google Form Timesheet Template.
Google Sheets Timesheet Report
The Google Sheet that stores the form's responses looks like this. Each response will be added as a new row to the sheet. Here is a copy of the Google Sheet Timesheet (Responses) Template.
Using this data, we'll create two weekly reports. The first report will show the weekly details for a selected employee, and the second report will show the weekly totals for all employees. The template for each report looks like the following.
Once we have our report design completed, we can add some formulas to lookup the values from the response data and calculate the hours worked by subtracting Time In and Breaks from Time Out. Multiply the result by 24 and you get the hours worked per day per employee.
Here are the the reports with data populated from the Google Form. Here is a copy of the Google Sheet Employee Timesheet Template.
In summary, we created a Google Form to accept employee timesheet data, saved the responses in Google Sheets, and built two weekly timesheet reports based on those responses.
Hopefully, I've demonstrated that Google Forms is a simple way to enter data and Google Sheets is a great tool to build reports based off of that data. The next time you are looking for a solution to track employee timesheets, you might want to consider using Google Forms instead of having users enter data directly in Google Sheets or Excel.
If you are looking for a custom timesheet or any Google Sheet/Form/Script project, I'm available for hire on Upwork.