Time recording in Excel: How to create a timesheet

More and more companies are in­tro­duc­ing flexible working hours in their business. This has clear ad­van­tages for pro­duc­tiv­i­ty and employee sat­is­fac­tion, 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 ac­count­ing. All you need for an elec­tron­ic timesheet is the Excel spread­sheet software - and our step-by-step in­struc­tions.

What makes a pro­fes­sion­al timesheet in Excel so special? Employees, employers, and lawmakers have quite different demands.

From the em­ploy­ee'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 ad­di­tion­al effort and in­ter­rup­tions to your workflow. The structure and design of an Excel timesheet must therefore make it im­me­di­ate­ly clear which in­for­ma­tion is relevant and where and how the cor­re­spond­ing data must be entered. A clear structure, helpful color scheme, and limiting every­thing to the es­sen­tials 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 per­for­mance as well as for payroll ac­count­ing, there are often some complex cal­cu­la­tions to be done. Ideally, the Excel spread­sheet should do these cal­cu­la­tions au­to­mat­i­cal­ly in order to save employees and employers time and effort. Cor­re­spond­ing functions can be in­te­grat­ed 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 es­pe­cial­ly ad­van­ta­geous 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 down­loaded free of charge from the following link and used straight­away:

In the following step-by-step in­struc­tions, we explain the in­di­vid­ual steps required to create the timesheet template.

Here is how you equip the header of the Excel spread­sheet with all the necessary in­for­ma­tion, 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 cal­cu­la­tion 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 de­part­ment), 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 con­sis­tent to create a better overview and be more visually appealing. Connected cells and different fill colors can help to separate in­di­vid­ual 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 hor­i­zon­tal menu.
    
3. Click the “Data Ver­i­fi­ca­tion” 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 “Val­i­da­tion 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]. Al­ter­na­tive­ly, 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 au­to­mat­i­cal­ly 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 for­mat­ting 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 com­pli­cat­ed 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 cal­cu­lat­ed 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. Al­ter­na­tive­ly, you can click on the “AutoSum” button in the “Formulas” tab and select the “Sum” option. Excel then au­to­mat­i­cal­ly de­ter­mines 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 cal­cu­lat­ing 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 sum­ma­rized 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 spec­i­fi­ca­tions ac­cord­ing­ly. Please refer to the screen­shot above and our down­load­able template.

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

You’ve now got yourself a pro­fes­sion­al 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 au­to­mat­i­cal­ly detects a recurring pattern and repli­cates all for­mat­ting, drop-down menus, formulas, and rules for all con­tin­u­ous 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 pro­tec­tion.

This is how to do it:

1. If the active Excel worksheet is “locked”, cells can’t be edited. To implement only partial write pro­tec­tion, you must first change this setting.
    
2. To do this, select the entire table by clicking on the point of in­ter­sec­tion 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 com­bi­na­tion [Ctrl] + [1].
    
4. Select the “Pro­tec­tion” 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.

Un­for­tu­nate­ly, this method does not include the formulas in the G and H columns. To include them, proceed as follows:

1. Press the key com­bi­na­tion [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 pro­tec­tion:

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 in­struc­tions that explain the procedure for recording working times in an un­der­stand­able way. Clarify, for example, which email address the completed timesheet should be sent to, and when. By spec­i­fy­ing 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.