Employee schedule control with Excel: easy guide
More and more companies adopt flexible work schedules , something that, although it has great advantages for employee productivity and motivation, must be based on a certain trust base. The registration systems of the working day are an instrument to achieve it, because, at the same time that it accredits the hours worked , it serves the management to control the quality and settle the weekly or monthly billing. All you need to manage a sheet for the hourly control of the template is the Excel spreadsheet software and follow our step-by-step instructions.
1. Calculate the hours worked with Excel: how to keep a professional record
2. Calculate hours worked with Excel: create your own worksheet
3. Control of workers’ schedules with Excel: implementation
Calculate the hours worked with Excel: how to keep a professional record
What distinguishes a well-planned Excel worksheet? In this regard, the employee, the employer and even the law have different points of view.
How a worksheet should be for the employee
From the point of view of the employee, a system of daily registration of the working day must be, above all, easy to use . After all, filling in the table each day with the hours of entry, exit and breaks becomes an extra task and involves a disruption of concentration. Already the design and structuring of the Excel worksheet must be clear enough so that the employee immediately knows what data to enter, how and where. To achieve this, design an easy structure , differentiate sections or cells with soft colors and reduce the design to the essentialIt can be a great help. After all, the simpler and clearer a task is, the more likely it is to feel like doing it every day.
Employer worksheet requirements
Since the registration of the day is so relevant to control the presence of the employee, as well as for the calculation of the salary, it sometimes implies having to use formulas of certain complexity that an efficient Excel table should include to alleviate the workload of Employees and management. The necessary functions can be inserted in the table invisibly and reduce the error rate in the long run.
Registration of the day in labor law
Spanish labor law extends into several decrees and laws passed and readjusted over the years. The central corpus is constituted by Royal Legislative Decree 2/2015, of October 23, which approves the consolidated text of the Workers’ Statute Law . This includes the regulations that affect the employment relationship between the worker and the employer, including the duration of the working day (art. 34), which should not exceed 40 hours per week and 9 hours per day, or breaks between the end of one day and the beginning of the next, which cannot be less than 12 hours.
With the entry into force in May 2019 of Royal Decree-Law 8/2019, of March 8 , on urgent measures of social protection and the fight against job insecurity during the workday, a point is added to article 34 in the one that specifies the obligation of the companies from this moment to guarantee the daily record of the beginning and the real end of the day, to organize this registration system by agreement or in collaboration with the legal representatives of the workers (company committee) and to keep all this documentation for four years, a period during which they will be available to employees, their legal representatives and the Labor and Social Security Inspection. With this measure, adopted following the recommendation made by Spain by the General Counsel of the Court of Justice of the European Union (CJEU), Giovanni Pitruzzella, and which had been blocked by the Spanish Confederation of Business Organizations (CEOE) repeatedly, It aims to curb labor exploitation in the form of unpaid overtime.
Calculate hours worked with Excel: create your own worksheet
In the example that we will show below, we will design the Excel worksheet for a single employee , who can enter at what time his or her work day begins and ends, as well as its breaks. With this data, the sheet will calculate how many real hours you have worked and will indicate, if applicable, whether you have worked more or less. In jobs paid for hours worked, this will save the employer or the company’s financial department time. At the same time, the employee will also be able to write down his vacation and the days he has been absent due to illness. The most important information can be highlighted using the visual format options of the spreadsheet.
In this link you can download the IONOS template to register the hours worked by your employees for free:
In the following instructions, we reveal how we created our template.
Step 1: Configure the header and columns
To assign all the necessary information to the first row of the table, configure the layout of the columns and give a clean format to the sheet, proceed as follows:
- Enter the name of the employee in cell A1 .
- Introduced into the cell D1 the workday employee in hh: mm (hours and minutes), p. eg, 08:00 (8 hours). This value will serve as a reference for the formulas that we will incorporate later.
- To make it easier for the employee to find the correct row, we will add a small but practical detail: the current date . Type the formula = TODAY () in cell H1 and confirm with the “Enter” key.
- Another useful detail: in the second row you can clarify to which email address (eg, from the HR department) the hours worked sheet should be sent and with what subject.
- In the third row , title the columns as “Date”, “Start day”, “Start pause”, “End pause”, “End day”, “Absence”, “Hours worked” and “More / Less”.
- When you visualize the table, you have some freedom to use bold and the different types, sizes and colors of the fonts. From the aesthetic point of view, the columns look clearer and more attractive if they have the same width. Combined cells and fill colors can help distinguish sections of the table from each other. The “Copy Format” button in the upper left corner of the ribbon speeds up design tasks.
- Finally, set the first three rows of the table so that, as the weeks are filled, they always remain in sight. This is achieved like this: mark cell A4 , go to the “View” tab and then click on “Freeze” first and then on “ Freeze panels ”.
Step 2: Insert a list of dates in sequence
To create a list of sequential dates and distinguish the weekend with colors, follow these steps:
- In cell A4 , write the first day , after which the workday has to be registered.
- In the “Start” tab and in the “Number” category, open the drop-down menu and select the ” Long Date ” format . This will cause the date to be displayed in long format, which includes the day of the week (you may have to use the date format dd / mm / yy for this function to give the correct result).
- Click on the small square that will appear in the lower right corner of cell A4 when you mark it and hold the mouse. Now slide the mouse down to cover seven cells for a full week.
- Now mark all columns. Click on column A, hold the left mouse button and drag it to column H.
- In “Start”> “Styles” click on the ” Conditional formatting ” function .
- In the context menu, click on ” New rule … “. A separate window will open.
- Under “New formatting rule,” select ” Use a formula that determines the cells to apply formatting .”
- In “Format the values where this formula is true,” write the following formula: = DIASEM ($ A1; 2)> = 6 (that is: “The following applies to all Saturdays and Sundays in column A”) .
- Click on the ” Format ” button . A new window will open.
- In the ” Source ” tab , select a color to mark the weekends and confirm the settings. Confirm again in “Accept”. Now, every Saturday and Sunday will have an assigned color.
Don’t panic: all these formatting actions, rules and formulas only have to be done once. To give these properties to other cells, you just have to drag the small square that appears in the lower right corner of the cell down or copy the cell.
Step 3: Configure options to register a work absence
This is the process to follow to insert a drop-down menu in the column to register different types of work absenteeism:
- In column F (“Absence”), mark the first cell that corresponds to one day of the week. In our case, it is the F4 .
- On the ribbon, go to the ” Data ” tab .
- In “Data Tools” click on ” Data Validation “. A window will open.
- In “Settings”> “Validation criteria”> “Allow” select ” List “.
- Uncheck the “ Skip targets ” box.
- In the “Source” dialog box, type the options that should appear in the drop-down menu, but don’t forget to separate them by semicolons. In our example it would be: ” Holidays; Illness; Home office “.
- Confirm the settings.
- A drop-down menu will now appear in the cell in column F. To give the same setting to the other days of the week, copy the settings or slide the square of the cell down.
Step 4: Calculate the hours worked with Excel
This is the way to program Excel to calculate the hours worked by an employee automatically:
- Mark column G (“Hours worked”).
- In the “Start” tab> “Number” select ” More number formats “.
- A new window will open. In “Category”, select “Custom”, and in “Type”, the box on the right, select the format [h]: mm: ss (with it, you order Excel to add the number of hours in the sum absolute, instead of starting again from scratch after 12:00).
- Confirm with “OK.”
- Now, copy this formula in the first cell of column G that corresponds to one day of the week (G4, in our case): = YES (O (F4 = “Holidays”; F4 = “Disease”); $ D $ 1 ; E4- (D4-C4) -B4).
This complicated function IF gives the following instruction to Excel: “If, for a date in column F (“ Absence ”),“ Vacation ”or“ Illness ”has been introduced, the 8 hours of work that the employee in column D1 owes they must be registered as effective hours worked (with “Home office” does not have to occur). If, on the other hand, column F is empty, working hours are computed normally following this formula: E4 (End of day) – (D4 (End of pause) – C4 (Start of pause)) – B4 (Start of day). As you intuit, you can incorporate this formula to the other days of the work week by dragging the lower square with the mouse. The $ D $ 1 formula ensures that the same workday (8 hours) is always taken as a reference.
Now the hours worked must be added to obtain the total number of hours worked throughout the week:
- In the first cell of column G that corresponds to a Saturday (in our example, it will be G9 ), write the following formula: = SUM (G4: G8)
- Optionally, in the “Formulas” tab you can click on the “ Autosuma ” button and select “Sum”. Excel then supplies the correct formula.
Step 5: How to calculate overtime in your Excel table
With a little trick, you will be able to program your Excel table so that it counts not only the overtime made by your employees, but also the negative ones:
- Format column H (“More / Less”) as [h]: mm: ss (using the same procedure here as you did in column G (“Hours worked”).
- Since Excel cannot represent by default any negative temporal value, we have to use a little trick in the ” Options ” of Excel. Click on “Advanced” and look in the “When calculating this book” group for the entry “ Use 1904 date system ”. Check the box and confirm the changes with “Accept”.
- In our example, in cell H4 we introduce the following formula: = G4- $ D $ 1 (this orders Excel to subtract the hours actually worked to the established day).
- Incorporate this formula to the rest of the days as you know.
- To add all the positive or negative hours, you can use the “ Autosuma ” function or write the formula = SUM (H4: H8).
Schedule control in Excel: basic formulas
In this section, we review the formulas that we have used and that you will need to create a record sheet of hours worked with Microsoft Excel. If you copy our 1: 1 example, you can use these same formulas without changes, but if you create a sheet with your own design , you will have to adapt the cell numbers in the formulas. To achieve this, you can orient yourself by the screenshot above or by the downloadable template.
|objective||Formula (in our table)|
|Current date||= TODAY ()|
|Mark the weekend in another color||= DIASEM ($ A1; 2)> = 6|
|Hours worked per day||= YES (O (F4 = “Holidays”; F4 = “Disease”); $ D $ 1; E4- (D4-C4) -B4)|
|Sum of hours worked||= SUM (G4: G8)|
|Positive and negative hours per day||= G4- $ D $ 1|
|Sum of positive and negative hours||= SUM (H4: H8)|
Control of workers’ schedules with Excel: implementation
Finally, we will explain how you can configure your template to be able to use it in the long term, partially protect it against writing and communicate it to your employees.
Reuse your day registration template
The result of your work is a professional template that, for now, only covers one week. To expand it , you can use our trick again: mark with the mouse all week, click on the small square in the lower right corner and, without releasing it, move the mouse down. As you can see, Excel recognizes that it is a recurring pattern and replicates all formats , drop-down menus, formulas and rules for all sequential dates. This is what will allow you to reuse your template as many times as necessary.
Partially protect your template against writing
Your employee can now record when their workday starts and ends, as well as their breaks, in columns B, C, D, E that you set up for it in the hh.mm format (e.g., 8:30) and also You can declare your vacation and your days off work in column F. But, apart from this, you should also be able to modify the content and structure of the table. For this purpose, partial write permissions are assigned to the table .
Proceed as follows:
- If the active sheet is protected, all cells have the “Locked” status by default. To implement partial protection, this setting must be changed.
- Mark the entire table by clicking on the upper left point where the columns and rows are located.
- Now, click on ” Start ” on the small arrow in the lower right corner of the ” Alignment ” group to open the ” Format Cells ” window . Another option is to type [Ctrl] + .
- Go to the ” Protect ” tab and uncheck the ” Blocked ” box.
- Confirm the changes.
- Hold down the [Ctrl] key and mark with the mouse all the cells you want to protect. We recommend that you mark rows 1, 2 and 3 and column A.
- Go back to the “Protect” tab and check the “Blocked” option again .
This method, however, does not include the formulas in columns G and H . In this case, follow these steps:
- Type [Ctrl] + [I] to open the ” Go to … ” window .
- Click on the ” Special ” button .
- Mark the point “ Cells with formulas ” and confirm in “Accept”. Now, Excel marks all cells that contain formulas.
- Follow the steps described above to block all formulas.
But, before sending the sheet to your employees, you must activate write protection:
- Click on ” Protect sheet ” on the ” Review ” tab .
- Under “Allow users of this spreadsheet,” uncheck the ” Select locked cells ” box.
- You have the option to protect the sheet with a password .
- Click on “ Accept ” (if you have given a password, you must confirm it again).
Now yes: send your Excel worksheet to your employees
Save your template in the regular Excel format and send it by mail to your employees, but do not forget to accompany it with the instructions for use to facilitate the understanding of its operation: clarify, for example, to which email address and when or in what period of time the sheet has to be sent. By indicating a unitary way of naming the sheet (eg, name, surname, date), it is easier to classify and organize the sheets.