How to Make a Timesheet in Google Sheets
Are you a small business owner who wants to create your own timesheets? Maybe you’re an employee who wants to keep track of things for your own records. Either way, learning how to make a timesheet in Google Sheets is very simple. With a few clicks and a little spreadsheet know-how, you could be on your way to an organized, neatly formatted, easy-to-use timesheet.
Just create a new blank file in Google Sheets, and follow the step-by-step guide below. It will take you through every step you need to know in order to create your own Google Forms timesheet.
How to make a timesheet in Google Sheets
Navigate to Google Sheets. At the top, you’ll see a heading called “Start a New Spreadsheet.” Click on the rainbow plus sign marked “Blank.” This will open up a new sheet for you.
Add column headers
Click on the first cell in the first row of the spreadsheet. This row is going to be your headers. You may wish to change the background color or bold the text in order to visually separate it from the data that will go below it, to make it easier for you to read later on.
The first header, in A1, should be “Date.” Don’t worry about the cells below it yet — just type the header for now.
The next, in B1, should be “Start time.” This column will hold all of the times you or your employees clock in.
C1 should be “End time.” This will be for clocking out.
If applicable, D1 should be marked “Breaks.” This will let you account for any time taken off during the day. If you’re paid for breaks, or don’t take them, feel free to skip this portion.
The last column, either D1 or E1, should be marked “Work Hours.” This will hold the total number of hours worked per day.
Apply appropriate number formatting to columns
Next, you want to make sure that your columns are properly formatted for Google spreadsheet time tracking. This will save you time, effort, and confusion later, and make sure that all of your dates and times have a consistent look.
To format date
First, format column A. This will hold all of your dates, and you’ll want them to have the same format from day to day and timesheet to timesheet.
Click on the column letter
Move your mouse cursor to the top of the column, marked “A.” Click on it to highlight the entire column.
Open the Format menu
In the Google Sheets menu, right below the name of your spreadsheet, there are several options. “Format” is fifth from the left, in between “Insert” and “Data.” Click on it, and it will produce a dropdown menu.
Hover over number and choose date
The second option in the dropdown menu is “Number,” with a right arrow. Hover over this to produce another menu to the right. Click on “Date.” Your column will now automatically format the data entered into it to be a date. This formatting only works if the data has the right number of digits and spacing, however. The number “1234567” will appear as “1234567,” but “1 23 4567” will be rendered as “1/23/4567.”
Format the start and end time columns the same way
You can do the same thing for your other columns. Click on their letters, go to the “Format” menu, hover over “Number,” then click “Time.” This will automatically format the data entered into 00:00. Note that it works using a 24 hour clock — entering 23:20 will be formatted as 11:30 PM, while entering 11:30 will become 11:30 AM. You can also enter 12 PM, which will become 12:00 PM.
Format the work hours and breaks as Duration
You can also format a cell to show you a duration. Click on the letters of your “Work Hours” and “Breaks” columns, go to the “Format” menu, then “Number,” and click on “Duration.” This is useful for work hours and breaks, or anywhere else where you need to see a total in a 00:00 format.
Add the timesheet start date under the date header
You’re halfway to learning how to make a timesheet in Google Sheets, and your sheet is almost ready to use! Click on the cell below your “Date” header, and type in the timesheet’s start date. It should automatically turn it into a MM/DD/YYYY format. If it doesn’t, repeat the step above to make sure the column is formatted appropriately, and double-check to make sure you’re entering the correct data.
To put the next date in the cell below, use “=A2+1”
This is probably the most challenging part of learning how to make a timesheet in Google Sheets, and all it takes is a simple formula. Rather than type in the date every time you use your hours tracker Google Sheets makes it easy to automatically fill in the rest of the date column. In cell A3, type the formula “=A2+1.”
Drag the formula down to cover all dates
Now, click on cell A3 where you just entered the formula. You should see a bright blue box highlighting the cell, with a square drag handle in the lower right corner. Hover your mouse over this square, and it will turn into a crosshair. Click the square, and drag it down to cover as much of the A column as you need. This will copy the formula from A3 to A4, A5, and so on. You should see the dates automatically fill in based on what you entered in A2, already formatted and ready to go.
Use the formula “=C2-B2-D2” to calculate work hours. Drag the formula down to cover all dates
Next, repeat the process for the “Work Hours” column. Go to the cell just below the “Work Hours” header, and type in the formula “=C2-B2-D2.” This will subtract the start time and any breaks from the end time, giving you the total hours worked.
Just like with the “Date” column, click on the cell that holds the formula, and use the drag handle to copy it down the rest of the column.
Make a copy
That’s all you need to do to know how to make a timesheet in Google Sheets. The only thing left is to make a copy of your hours tracker Google Sheets file to use for later dates (or other employees.)0 Click on the empty box to the left of column letter A, to select the whole sheet. Copy it with CTRL + C, or by clicking on “Edit,” then “Copy.” You can also right click the spreadsheet and choose “Copy.” All of your dates, formulas, and other information are now copied to your clipboard, so you can paste them into a new spreadsheet.
Free Google Sheets timesheet template
You can use the above steps to create your own free time card template Google Sheets, or you can grab an existing one from the internet. There are tons of free templates available, and all you need to do is copy them, paste them into a new spreadsheet, and start filling them out.
Weekly timesheet template Google Sheets
What if you want to explore different ways to work with a Google Sheets time tracking template? You aren’t limited to the style above — there are loads of free ones to fit a variety of needs, including daily, weekly, and monthly timesheet template Google Sheets.
Free template in Google Sheets Template Gallery
Google Sheets Time Tracking Template
You’ve learned how to make a timesheet in Google Sheets. You’ve even picked out a Google Sheets time tracking template, and you’re ready to start using it. what comes next?
Go to the Template Gallery
At the top of the Google Sheets main page, just below the search bar, is the template gallery. Chances are, yours only shows the basic ones — to-do lists, budgets, and so on. Click on the up and down arrow icon to the right of the “Template Gallery” label.
This will show you all of the publicly available templates, as well as any that you’ve downloaded and installed to your Google Sheets. For this example, look for the one marked “Schedule,” and click on it to use it.
The template is already formatted
This will open up a new spreadsheet, already formatted for use as a basic schedule.
Customize to meet your needs
You can tweak this basic template in any way you need to. Adjust the formatting, add new formulas, change fonts and colors, and do whatever you wish to make it work for you. You can even use the basic scheduling template to create your own weekly timesheet template Google Sheets.
Can be used offline
If you need to be able to use your timesheet or template offline, that’s no problem. To use your Google Sheets files offline, you’ll first need to get the Google Docs Offline extension for Chrome or Microsoft Edge. Once you have that setup, go to your main Google Sheets page. It will have a list of your spreadsheets, and, to the far right, a kebab menu icon (three vertical dots). Click on this icon, and choose “Available offline.” They’re now ready for you when and wherever you need them.
Whether you want to know how to make a timesheet in Google Sheets, or just want to find a larger library of professionally made templates, Google Sheets makes it easy. The simple steps above can take you from a blank spreadsheet to sleek, professional-looking daily, weekly, or monthly timesheets in no time flat.
The above article may contain affiliate links, which help support Clever Sequence