Define your labels and date ranges
First of all, you need to figure out what kind of information you actually need to have on your timesheets. Typically you would need a field for employee name, the time period the timesheet covers (week, month), but also the name of department & manager.
Of course, you can also add fields for social security numbers, employee IDs if needed, but simpler is better.
Use columns to break up the days
In this example, the fields for morning working hours and afternoon hours are separated, to account for the longer lunch break in the middle of the day. Also, by entering hours in the timesheet twice a day, the employee won't have to remember all their day's hours at the end of the day.
Breaking up the day, however, means that now we have to add up the hours. The next step shows you how to calculate the hours automatically.
Calculating daily working hours
In this example timesheet, we will be using two basic formulas - one for adding up the total hours for a single day, and one for adding up all working hours for the week.
To add up a single day's hours, insert a simple =SUM(B7+D7) formula into the "Total hours" cell at the end of the first work day (E7 in this example). Once an employee has typed in their hours in the "morning" and "afternoon" cells, they will be automatically added up in the "total" field.
To use the same formula for the other days, simply right click and copy the cell containing the formula and choose "paste formula" for the remaining total hour cells - Excel will automatically change the formula for the other rows, so no need to do this manually!
Calculating total weekly hours
To get the total hours your employee worked in a week, you will need to add up all the daily total hours for that week. To do that, pick a cell where you want the weekly total count to be displayed and insert another SUM formula.
For example in this example, the total daily working hour fields are all in column D, starting at row number 3 and ending with row number 8. To calculate the weekly total hours, the formula would be =SUM(E7:E11). This formula adds up all values in column D that fall between rows 3 and 8.
Locking cells to prevent editing
Once you've added these formulas and formatted the sheet to your liking, you will have a perfectly functional, easy-to-use timesheet. Now it's time to protect your design from unwanted changes.
To avoid someone accidentally deleting a formula or breaking your formatting, you can limit the cells your employees can edit by locking them. Typically, your employees would only need to manually insert the "morning" and "afternoon" working hours, but you can also choose to let them add their own names and edit the date ranges.
By default, all cells in Excel are set as "locked" - to designate the cells you want your employees to be able to edit, drag and select these cells, select "format cells" and under the "Protection" tab, untick the checkbox that says "locked".
At this point, you have only defined which cells should be "unlocked" - for these changes to have an actual effect, you need to enable the "protect sheet" settings.
Locking the timesheet
Once you have unlocked the cells you need your employees to edit, head to the "Format" menu and select "protect sheet">. You will see a long list of options, but you only need to tick the box where it says "select unlocked cells".
When you've ticked the box, choose a password for unlocking the timesheet, hit "OK" and you're done. But make sure you don't forget the password!
Simply copy the timesheet file and pass the copies on to your employees so they can start logging their time.
Use Toggl Track to skip all this work
Ultimately, Excel timesheets are tricky to manage and not very accurate. This is why we created Toggl Track for tracking work time.
It's very simple to use and easy to set up. With options for tracking in real time and filling in work hours later, it's basically a much more accurate and versatile version of a timesheet. On top of that, Toggl Track has a powerful reporting feature that automatically sorts your timesheet data into customizable reports that are easy to share - and all of this is stored safely in the cloud.
Feel free to check it out by following the link below - Toggl Track is free for small teams!