Time recording in Excel: How to create a timesheet

More and more companies are introducing flexible working hours in their business. This has clear advantages for productivity and employee satisfaction, but also requires a certain level of trust. A system for recording working time is usually used for this purpose. This serves as proof of how much the employee has worked and the boss can then use it for quality control and weekly or monthly accounting. All you need for an electronic timesheet is the Excel spreadsheet software - and our step-by-step instructions.

What makes a professional timesheet in Excel so special? Employees, employers, and lawmakers have quite different demands.

From the employee's point of view, a time recording system must be easy to use. After all, regularly having to document your own working times and break times means additional effort and interruptions to your workflow. The structure and design of an Excel timesheet must therefore make it immediately clear which information is relevant and where and how the corresponding data must be entered. A clear structure, helpful color scheme, and limiting everything to the essentials can help a lot. Basically, the easier you make it for your employees, the more likely it is that the time recording will be regularly filled out, delivered on time, and be correct.

Since recording working time is relevant for checking employee performance as well as for payroll accounting, there are often some complex calculations to be done. Ideally, the Excel spreadsheet should do these calculations automatically in order to save employees and employers time and effort. Corresponding functions can be integrated into the file and will reduce the amount of errors for regular time recording in the future.

If an employee works more than 40 hours in a week, the Fair Labor Standards Act (FLSA) requires them to be paid overtime at the rate of at least one and a half times their normal rate of pay. Some states have overtime laws so in cases like these where an employee is subject to both the state and federal laws, they adhere to whichever gives them the higher overtime pay. This is why time recording is especially advantageous since it’s easier to work out if the employee has exceeded the 40 hours and is required to be paid a higher rate for these extra hours by law.

The Excel timesheet template from IONOS can be downloaded free of charge from the following link and used straightaway:

In the following step-by-step instructions, we explain the individual steps required to create the timesheet template.

Here is how you equip the header of the Excel spreadsheet with all the necessary information, set up the columns, and format your timesheet clearly:

1. In cell A1, enter the name of the employee.
    
2. In cell D1, enter the daily target working time of the employee using the format hh:mm (h = hours, m = minutes), for example, 08:00 (8 a.m.). This value serves as a reference for the calculation formulas that we plan to include later in the table.
    
3. A small aid to help your employees: a current date display, which makes it easier for them to find the right row in the time sheet. To do this, enter the formula =TODAY() in cell H1 and confirm by clicking on “enter”.
    
4. Another aid: In row 2 you can explain which email address (e.g. the HR department), the timesheet should be sent to and which subject the email should have.
    
5. In row 3, enter the column headings “Date”, “Time in”, “Start of break”, “End of break”, “Time out”, “Absences”, “Actual working hours” and “Plus/minus hours” one after the other.
    
6. Format your table as you like. Use bold writing and different fonts, sizes and colors as you see fit. Keep the column widths consistent to create a better overview and be more visually appealing. Connected cells and different fill colors can help to separate individual areas from each other. The “Transfer format” button in the upper left corner of the menu helps you to speed up the design process.
    
7. Finally, freeze the upper three rows of your table so that they remain visible to the employee even when they scroll down. To do this, select cell A4. Now click on the “Freeze panes” button under the “View” tab and select “Freeze panes”. Now all rows above cell A4 are fixed in place.

How to add a drop-down menu to the absences column of your timesheet table:

1. In column F (“Absences”), select the first cell that belongs to a weekday. In our case this is F5.
    
2. Select the “Data” tab in the horizontal menu.
    
3. Click the “Data Verification” button in the Data Tools section. A new window will open.
    
4. Select in the “Settings” tab, the “List” option, which can be found under “Validation criteria” > “Allow”.
    
5. Remove the check mark for “Ignore blank”.
    
6. In the “Source” dialog box, type the options you want to be available in the drop-down menu. Remember to separate the options with a semicolon. In our example it looks like this: Vacation;Illness;Mobile Office.
    
7. Confirm your entry with “OK”.
    
8. The cell in column F now has a drop-down menu added. To add it to the other days of the week, simply copy it using [Ctrl] + [C] / [Ctrl] + [V]. Alternatively, you can use the small square in the lower right corner of the cell, which is a faster way.

This is how you can get Excel to automatically calculate the actual working hours of your employees:

1. Select column G (“Actual working hours”).
    
2. Under “Home” > “Number” click on the small arrow next to “Number” in the box and select “More...” at the end of the drop-down menu. A new window will open.
    
3. Under “Category”, select “Custom” and enter the following formatting in the dialog box under “Type”: [hh]:mm (this commands Excel to include the absolute number of hours in the total instead of always starting from zero after 12:00).
    
4. Confirm the entry with “OK”.
    
5. Copy the following formula into the first cell in column G that belongs to a weekday (in our case this is G5): =IF(OR(F5="Vacation";F5="Illness");$D$1;E5-(D5-C5)-B5).

This rather complicated looking if-then formula tells Excel the following: “If in column F (“Absences”), “Vacation” or “Illness” is entered instead of a date, the 8 target daily hours from cell D1 should be recorded as actual working hours (if “Mobile Office” is entered, nothing should happen). If, on the other hand, column F remains empty, the working hours should be calculated regularly according to the following formula: E5 (time out) - (D5 (end of break) - C5 (start of break)) - B5 (time in)." As usual, you can apply this formula to all other days of the week using the small square. The formula $D$1 ensures that the same target daily hours are always used as a reference.

Now the actual working hours have to be added to the number of weekly hours:

1. In column G, in the first cell that belongs to a Saturday (for us this is G10), enter the following formula: =SUM(G5:G9).
    
2. Alternatively, you can click on the “AutoSum” button in the “Formulas” tab and select the “Sum” option. Excel then automatically determines the correct formula.

Here’s how to get Excel to calculate both plus and minus hours for an employee:

1. Format the column H (“plus/minus hours”) as [hh]:mm - use the same procedure as for column G (“actual working hours”).
    
2. Since Excel cannot display negative times by default, you have to make a small detour to “Options”. Click on “Advanced” and search for “When calculating this workbook:”. Set a checkmark in front of “Use 1904 date system”. Confirm the entry by clicking on “OK”.
    
3. In our example, we now enter the following formula in cell H5: =G5-$D$1 (this tells Excel to settle the actual working hours with the target working hours).
    
4. Transfer the formula to all other weekdays as usual.
    
5. To total the plus/minus hours, you can use the “AutoSum” function again or enter the formula =SUM(H5:H9) manually.

In the following section, we have summarized all the practical formulas that you need for Excel time recording. If you copy our example directly, you can adopt these formulas without needing to make any changes. If, on the other hand, you want to create your own timesheet design, you must adjust the cell specifications accordingly. Please refer to the screenshot above and our downloadable template.

Lastly, we’ll explain how to reuse your completed Excel time recording template, equip it with partial write protection, and send it to your employees.

You’ve now got yourself a professional Excel timesheet template that currently only covers a single week. To extend it to any amount of time, apply this trick again: Select the entire week including all columns with the mouse, click and hold the small square in the lower right corner of the selected area and drag it downwards. As you can see, Excel automatically detects a recurring pattern and replicates all formatting, drop-down menus, formulas, and rules for all continuous data. This allows you to reuse your template over and over again.

Your employee enters the times that they start work and when their breaks begin, as well as when they finish work and what time their breaks end in columns B, C, D, E using the hh:mm format (e.g. 08:30). They enter vacation and sick days in column F. Apart from this, they don’t need to make changes to the contents and structure of the timesheet. For this purpose, it makes sense to equip the Excel table with a partial write protection.

This is how to do it:

1. If the active Excel worksheet is “locked”, cells can’t be edited. To implement only partial write protection, you must first change this setting.
    
2. To do this, select the entire table by clicking on the point of intersection between the columns and rows (top left).
    
3. Now click on the small arrow in the lower right corner of the “Alignment” area under the “Home” tab to open the “Format Cells” dialog box. You can also use the key combination [Ctrl] + [1].
    
4. Select the “Protection” tab and uncheck the “Locked” checkbox.
    
5. Confirm the entry with “OK”.
    
6. Hold down the [Ctrl] key and use the mouse to select all the cells you want to protect. We recommend rows 1, 2 and 3 as well as column A.
    
7. Return to the window with the “Locked” option and add the checkmark again.

Unfortunately, this method does not include the formulas in the G and H columns. To include them, proceed as follows:

1. Press the key combination [Ctrl] + [G] to call up the “Go to” dialog box.
    
2. Click on the “Special...” button.
    
3. Check “Formulas” and confirm with “OK”. Excel will now select all cells that contain formulas.
    
4. Proceed as described in the previous tutorial to set all formulas to “Locked”.

Before you send the timesheet to your employees, you must activate the write protection:

1. Click on the “Protect sheet” button in the “Review” tab.
    
2. Under “Allow all users of this worksheet to:” remove the check mark for “Select locked cells”.
    
3. It’s also possible to set a password to protect the worksheet.
    
4. Click “OK” (if you have entered a password, you will need to confirm it again).

Save your timesheet in regular Excel format and send it to your employees by email. Remember to include instructions that explain the procedure for recording working times in an understandable way. Clarify, for example, which email address the completed timesheet should be sent to, and when. By specifying a fixed format for naming the Excel files (e.g. first name, last name, calendar week), it’s easy to assign the timesheets to the relevant employee later.