More and more companies are offering their employees flexible working time models. After all, not only does employee satisfaction benefit from this, but also productivity.
However, the basis for flexible working hours to be successfully implemented is trust. This should be supplemented by a systematic recording of working hours, with which the employees can document their timesheets to their superiors. They in turn benefit from this in terms of billing and quality control.
To create a digital timesheet, nothing more is needed than the popular spreadsheet software – Excel. The following article shows how the basic structure for recording working hours can be set up with Excel.
This is what the time recording with Excel does
Employees can enter the times they started or ended their shifts in the time sheet, which can be created very simply and easily using Excel. In addition, the corresponding break times must also be documented.
The working hours are then automatically calculated by the software. At the same time, the accumulated plus and minus hours of the employee are calculated. The effort of the employer in payroll accounting can be significantly reduced in this way. In addition, the employees have the opportunity to record their sick days and vacation days in writing, because these are also automatically recorded by Excel.
If a clear color design and formatting is also selected in the program, the most important information, such as the minus and plus hours as well as the actual working hours, can be read at a glance.
Easy guide for Excel time tracking
Step 1
The first step in time tracking using Excel is to set up the columns and header.
The header is to be provided with all relevant information and the columns are to be formatted in such a way that the digital timesheet is as clear as possible. The header contains the employee’s name and the planned working time in the format hh:mm. A current date display should not be left out either. The formula =TODAY can be used for this.
Line three of the document is then given the designations date, start of work, start of break, end of break, change of work, absence, actual working hours, and plus/minus hours.
The table can basically be formatted according to personal taste, for example by using different font colors, sizes, and types. The width of the individual columns should be uniform for the sake of clarity. The final step of setup is to freeze the top three rows of the table so that they remain visible even as the document fills and requires scrolling.
Step 2
The next step is to equip the table with a consecutive date. The weekends are highlighted in color. It is then necessary to establish a new rule for the columns. The rule type to choose for this is “Use a formula to determine which cells to format”. The formula itself is: “=WEEKDAY($A1;2)>=6”
In addition, the absence options can be configured as a practical drop-down menu for the digital timesheet. To do this, mark the absence column in the first line. Then proceed down the menu to the Source dialog box. The options that the drop-down menu should contain, such as illness, vacation, or home office, can then be entered there.
For the actual working hours to be calculated automatically, the corresponding column must be marked. In the context menu, the “More” option must first be selected, then the “Custom” button under “Menu” is selected. In the “Type” dialog field, the formatting [hh]:mm is to be selected. The first line then has to be equipped with the formula =IF(OR(F5=”Vacation”;F5=”Illness”);$D$1;E5-(D5-C5)-B5).
