This is a short visual guide on how to build a simple Excel timesheet with automatically calculated hours that can be easily shared with your employees.
The example we are showing here only uses two basic Excel formulas, meaning that this timesheet template can be copied, changed and customized with ease, depending on your organization's needs.
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.
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.
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!
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.
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.
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.
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 signing up below - Toggl Track is free for small teams!
Teams of 5+ are eligible for a personalized demo to see how Toggl Track can meet your time tracking goals