Small businesses need to keep track of employee time. But, as a startup you can’t afford the expensive softwares. Come with us into Google Sheets as we show you how to build a timesheet template from scratch. By the end of this video you’ll have a timesheet your employees can fill in every day. Let’s Get Started! Do watch a video version of this post click here. Or continue reading below!
I usually start every worksheet with a title. Here we will title this one Timesheet. But every good worksheet also needs a description. Most all of my sheets will have the company name on top followed by the title of the schedule and the date. You can have your employees fill in their name at the top of the sheet, too.
If you are a beginner you may want to know
How Do I fill a series of Days of the week in Google Sheets?
In Cell B5 we’re going to put in Sunday as the first day of the week. We could go from Cell C5 to H5 and manually enter in the days of the week. But, Google has a feature to auto fill in the days for us. While still on cell B5 use your mouse to hover over the lower right corner of B5 until the arrow changes to a + then right click and drag the anchor across to H5 and release the mouse click. If done right Monday through Saturday will fill in like this across the page and that is how you can fill series the days of the week. This also works with other series common to spreadsheets. But we need to move on with our timesheet build so,
How Do I make the date appear in Google Sheets Timesheet Template?
The easiest way to see a date in a new worksheet is to just key in the date as you would like to see it. We’ll key in 7/7/2024 and it formats as keyed in. But, maybe you don’t like the date format exactly that way. While still on the date in cell B6, click on Format in the menu bar, hover over number, then Custom Date and Time. Here you can choose many date formats and find one that you like. But if not you can modify it. For example I can change these dashes to slashes.
There is a lot to learn here. But for now let's add a formula to update the week of dates by the date in B6. So, we’ll key in cell C6 =C6+1 and copy c6 from d6 to h6. But now that is done the days and dates aren’t centered. So, we’ll select the range and use the horizontal align button to center each cell and add a bottom border with the borders button.
Timeclocks are setup to punch in and out times. Since small businesses usually don’t have a timeclock we’ll add in and out times here with a couple of breaks to be consistent and subtotals. Depending on your business you may not need all of these but let’s add 2 breaks and a lunch to this timesheet.
Now we need to ask the question:
How do I insert Time in Google Sheets?
As your staff work throughout the day they can put in the exact time with a shortcut key. If we go to cell B7 and Ctrl+Shift+; will put in the current time. But, most small business will pay in 15 minute increments. So, I would advise you train your staff to fill in the time and round to the nearest 15 minutes. So, let’s put 8:00am in cell B7 and 10:00am in cell B8. Let’s also put in 10:15am in B12 and 12:00pm in B13. After lunch let's enter 1:00 PM in B17 and 3:00pm in B18. Note that Google needs the AM and PM to understand the entry unless you use 24 hour time. Google will recognize the time entered with a colon but in case you need to format the cell, click format on the menu, then hover on number, and select time. But, we don’t want seconds. So, we will click format on the menu, then hover on number, and select Custom Date and Time and choose the format with hour, minute, and AM/PM. Now that we know how to enter time we can copy that format throughout the sheet. I like using shortcut keys. So, I’ll use Ctrl-C to copy and then use my mouse to paste special the format only as needed.
Now that we have time filled in we can find out
How can I calculate the difference between two times in Google Sheets Timesheet Template?
To calculate the time worked in this first entry create a formula in B9 =B8-B7. Copy that formula throughout the spreadsheet. This will allow us to calculate the hours worked for each time in and time out entry during the week. I’m going to add some more in out times here to make sure everything is working well. Here you will notice that the time worked is being calculated as time. So, let’s convert it to a number and see what happens. Here you can see a decimal, so this doesn’t work. But there is a solution. In cell B9 enter the formula =hour(B8-B7)+minute(B8-B7)/60 and hit enter and like magic time is converted to a number that makes sense and yes google sheets can calculate hours worked. Now you can copy that formula throughout the worksheet.
But, we don’t have how many hours worked by day yet so,
Can Google Sheets Calculate Hours Worked?
We’re building up to this well so far. We have been able to enter time in google sheets in a readable and understandable format. We have also calculated the difference between a start time and end time. But, we need to go further and add multiple time entries per day. So, in Cell A25 I’m going to enter TOTAL to describe the row and In Cell B25 there are a couple of ways to do this. The easy way is to enter the formula =b9+b14+b19+b24. But another way to sum the four sections is in C25 to enter =sum(c9,c14,c19,c24). Either formula provides a similar result. Copy these totals to the rest of the sheet. Now we need to total the hours per day to get a weekly total. In cell F26 Title the result as Total Hours For The Week and in cell H26 enter the formula =sum(b25:h25). Google will actually suggest this formula as you type.
Now All we have left to complete our Google Sheets Timesheet Template is to clean up the page a little with borders. Put a border above each total row using the borders button. I select the cells above the total click borders and select the bottom border. But, you could also select the totals row and use the top border. But for the final in out total you will want to put a border above and below the total. And let’s put a border above the total hours like this. And a double border under the total.
Comments