How to Make a Timesheet in Google Sheets [Complete Tutorial]

Hi, today we will learn how to make a timesheet in Google Sheets. Clockwork needs an efficient payroll system to maintain records for paying workers. Workers need to turn in their time records on time, and then an accountant can issue a payroll budget to the worker after completing a schedule, Workers get their pay at the end of the week or a month.

Google Sheets has automated this process digitally by making a Timesheet in Google Sheets. You can use a lot of tools for creating a timesheet but Google Sheets offers it for free. No matter whether your staff is paid hourly, or you need to track their time for a project or another reason entirely, go through with the following article on how to make a timesheet in Google Sheets and create an efficient payroll system for yourself.

Benefits of Making Timesheets in Google Sheets

Either you are owning a small business or work as a freelancer. You might need to track your time to ensure timely payment.

Accurate time tracking is essential in any work environment for organizing records, payment processes, prevention of errors, and a flexible patrolling system. You must know how much your worker is working. Making a Timesheet in Google Sheets can give you paperless and unforgettable record management for the payroll system.

How to Make a Timesheet in Google Sheets

The procedure of making a Timesheet in Google Sheets depends on your set of mind and what else you require in your Timesheet. In this tutorial, I will convey the basic requirements for a Timesheet in Google Sheets through which you can create an automatic payroll system for your business. So let’s move towards the steps below.

Step 1

First I will insert a title for Timesheet in Google Sheets, to insert a title, I am going to use the Google Sheets drawing tool. To access the drawing tool of Google Sheets go into the “Insert” tab of the menu bar then click on the “Drawing” option from the drop-down menu.

Step 2

When you click on “Drawing“, a new window will open in front of you, where you can draw anything with the help of the given tools. To make a title, here I am using “Wordart” which you may find in the “Actions” tab located at the left top corner of this window.

Step 3

When you click on the “Word Art” option, a drop box will open, Write the title in this box and then press the enter button. You can also format your word art with the following tools. Once you have done that, click on the “Save and close” button.

Step 4

As you can see below, the title for the Timesheet has been added. After adding the title here I am going to add a little basic information such as “Employee name” and “Timesheet period“. You can add anything according to your criteria.

Step 5

Now, I am starting to create a Timesheet table, in which I will add the following highlighted headings, Date, Time In, Time Out, Working Hours, Regular Hours, and Overtime. You can add or remove headings if you need to.

Step 6

Let’s start writing the date for which period we are making our Timesheet. As we have mentioned above, this Timesheet starts from the 19th August so here we are inserting the first 19th August and so on.

Step 7

When you enter the data in the Google sheet cell, it will automatically detect it and format it in the format of data. But if it is not so then go into the “Format” tab of the menu bar of Google Sheets.

Step 8

A drop-down menu will open where you will see a “Number” option, when you click on this Number option, another drop-down menu will open, click on “Date” format to format your text as date.

Step 9

Now start taking a reading in the Time In and Time Out column day by day.

Step 10

Once you have completed inserting Time In and Time Out then select them all by hovering the cursor on it to change its format from 12h to 24h.

Step 11

To change the time format, again go into the “Format” tab of the menu bar, then click on “Number” from the drop-down menu to expand it more where you will see “Custom data and time” as highlighted in the following picture.

Step 12

When you click on the “Custom date and time” option, a small pop-up window will open where you will see different kinds of date and time formats. Select the 24-hour format from here and then click on the “Apply” button to save these changes. The 24-hour format will help to make calculations in Timesheet.

Step 13

The next section in our Timesheet is to find “Working hours“. To find working hours, we will just subtract Time Out from Time In. Let me show you the formula, Simply we will write the Time out cell address, minus sign, and Time In cell address then multiply the whole value by 24 to convert your result in the time duration format as highlighted below.

Step 14

Once you have got the result for the first day then you can simply drag it down to find the working hours of the other days.

Step 15

To find regular hours, we will use the Minimum value formula. We can also write the regular hours as at is without formula as they are constant. But you may get an error when the employee works less than regular hours in a day. To apply the minimum formula we write the syntax “=Min(8, working hours cell address)“.

8h is the constant for daily routine, but if someone works less than regular hours then insert the hours that he worked.

Step 16

Now you can understand by seeing the result in the Regular Hours column.

Step 17

The last section of our Timesheet is to find “Overtime“. To find Overtime, we will just subtract working hours from regular hours as can be seen in the formula in the following picture.

Note: If you don’t apply the Minimum formula in regular hours then you may get negative values in OT hours if an employee works less than regular hours.

Step 18

Let’s calculate, how many hours an employee has worked in a week. To find total working hours apply the sum formula simply as I have applied in the following picture. In the same way, calculate the total OT hours.

Step 19

Once you have got the total working hours and total OT hours then add them together to find total working hours in a week by the employee.

Step 20

Usually, Timesheets are used to calculate the hourly earnings of an employee. Let me tell you how to calculate it. Let’s suppose, your rates are 15$ per hour.

Step 21

To calculate the total earnings for an employee simply, multiply the total working hours by the rate per hour value, as you can see in the syntax in the following picture.

Step 22

The result can be seen in the following picture, as the total earnings for an employee for the week are $795 as resultant below.

That’s all done from mine, with the help of the above strategies you can create your timesheet easily.

Frequently Asked Questions

Is there any timesheet template in Google Sheets?

If you are talking about Google Sheets, then there is a template in the Google Sheets template gallery namely, “Employee shift Schedule” that can be much more effective to make a professional timesheet in Google Sheets. To access this template just open the Google Sheets template gallery click on the “Employee shift Schedule” template and start editing. However, you can also download hundreds of templates for timesheets from the web and then can easily import them to your Google Sheets.

Can REGEXMATCH be used to create a timesheet in Google Sheets?

Conclusion

Now you know how to make a timesheet in Google Sheets, no need to hire someone to create a payroll system for your business or any other paid tools. Go through the above article on how to make a timesheet in Google Sheets and create a timesheet for yourself free of cost.