Overtime template in Excel
Overtime template in Excel
Some weeks do not fit inside the working day: an urgent order has to be closed, a truck arrives after hours, someone covers the shift of a colleague who called in sick. Those hours get paid, and often they get paid badly, not out of bad faith but because nobody wrote them down with their type and their premium. When the month closes, the account is rebuilt from memory, the worker complains and whoever pays has nothing to answer with.
The problem is almost never the rate: it is the record. A daytime overtime hour, a night hour and a Sunday or holiday hour are not worth the same, and paying them means knowing how many hours of each type there were, which hourly value they are calculated on and which multiplier was applied. When those three things sit in a single row, the settlement becomes a multiplication and stops being an argument.
This Excel template does exactly that: it records overtime and shift premiums with the hour type, the quantity, the hourly value and the multiplier, and calculates the amount payable on its own. It holds two hundred rows, a drop-down list of hour types, a block with three editable multipliers in the header and a summary by hour type. It is an .xlsx file, with no macros and no add-ins: you open it, save it under the company name and start using it the same day.
⬇ Download the template (Excel .xlsx)What it is and who it is for
It is an internal control tool for settling the overtime and premiums of a period, not an official payroll document. Its purpose is for whoever manages staff to know, before paying, how many night hours, how many Sunday hours and how many holiday hours were worked and what they add up to. One point is worth stating up front: the sheet does not impose legal rates and does not carry fixed multipliers. The multipliers are input cells that each company sets to its own case, and the formulas read them from that block in the header. Changing a multiplier immediately changes every amount payable in the period, without touching a single formula.
It is for a personnel officer, an accountant, an administrator or the owner of a small business with shift workers, who needs to know what the extra hours cost without building a payroll module. When there are few workers and overtime is occasional, this sheet is enough. When the volume grows or there are several sites, the same logic moves into a system and the file remains as the support for what was already paid.
It is also worth being clear about what this sheet is not. It does not replace the payroll settlement or the other items of the period: it is the detail that supports a single line of the payment, the one for overtime and premiums. That is why it is best kept on its own, reviewed before payroll is closed and attached as an annex, so that any difference is settled with the record in view and not with an argument about how many hours there were.
How this sheet differs from a timesheet of hours worked
A timesheet of hours worked settles the ordinary day: clock-in, clock-out and the value of normal hours. This template starts where that one ends. Here it is not so important how long the day lasted, but how many of those hours went beyond the normal day and with what premium they are paid. The two sheets complement each other: the first says how much was worked; the second says how much of that is paid with a premium and under which concept. Anyone keeping both can close payroll without asking where a figure came from.
What the file contains
The file comes ready-made and there is nothing to build: these are the parts it is made of.
| Element | What it brings |
|---|---|
| Period log | Two hundred rows, one per overtime record, with the totals row at the end. Enough for a busy month and reusable for more if the sheet is copied. |
| Hour type list | Four options: daytime, night and Sunday or holiday. That choice decides the multiplier the formula applies. |
| Multiplier block | Three editable cells in the header, one for each premium the company uses. They are written once per period and read by the whole automatic column. |
| Multiplier column | Automatic. It takes the value from the header block according to the hour type written in the row. |
| Amount payable column | Automatic. It multiplies the quantity of hours by the hourly value and by the multiplier. |
| Period totals | Sum of hours recorded, sum of hours by premium and sum of the amount payable. |
| Summary by hour type | A short table with the hours and the value of each type, to see at a glance where the weight of the period sits. |
| Period header | Company name, period, person responsible for the calculation and the block of multipliers. |
The columns of the sheet
Eight columns, in the same order, one row per overtime record.
| Column | What you write | Calculates itself |
|---|---|---|
| Date | The day the overtime was worked, not the day it was written down. It orders the log and allows the period to be closed. | No |
| Employee | The worker name, always spelled the same way so that the period summary groups it correctly. | No |
| Hour type | Daytime, night, Sunday or holiday, chosen from the drop-down list. It defines the multiplier that is applied. | No |
| Quantity of hours | The hours of the record, in hours and fractions, for example 1.5. Never in minutes. | No |
| Hourly value | The ordinary hourly value that serves as the base for the premium. It is an input, not a fixed figure. | No |
| Multiplier | Read from the header block according to the hour type of the row. | Yes |
| Amount payable | Quantity of hours times hourly value times multiplier. | Yes |
| Notes | What does not fit in the other columns: whether the hour had prior authorisation, the reason or the shift it belongs to. | No |
The two automatic columns do all the calculation work. The multiplier is not typed by hand in every row: it is looked up in the header block and changes only when the hour type changes. The amount payable, in turn, appears only when there is a quantity, an hourly value and an hour type; if one of the three is missing, the row stays at zero and the total is not contaminated. That behaviour is intentional: it forces the record to be completed before the summary is trusted.
How the premium is calculated: an example with numbers
Let us see how the amount payable is formed with a short case.
| Item | Value |
|---|---|
| Hour type | Night |
| Quantity of hours | 4 |
| Hourly value | 6,000 |
| Multiplier | 1.40 |
| Amount payable | 33,600 |
The sum is a single one: four hours times 6,000 give 24,000, and that amount times 1.40 gives 33,600. If the same record had been marked as daytime with a multiplier of 1.00, the amount payable would be 24,000. The difference is not in the formula, it is in the multiplier the company decides, and that multiplier is an editable cell, not a rule hidden inside the sheet.
With that criterion, the period summary is read in two steps: first the hours by type, to learn where the extra hours were concentrated, and then the amount they add up to. If one hour type weighs far more than the others, there is usually a badly planned shift or a wrongly classified clocking, and that is fixed in the operation, not in the payment.
Step by step to settle the period
The order matters: first the criteria for the period, then the record and finally the review. If you type before setting the multipliers, you will have to go through the whole period again.
- Open the file and save it under the company name and the period, for example overtime for March.
- Write the three multipliers you will use in the header block, before typing the first record, so that the formulas take them from the start.
- Enter one record for each occasion: the date, the employee, the hour type, the quantity and the hourly value. The multiplier and the amount payable fill themselves in.
- Check the summary by hour type and compare the night, Sunday and holiday hours against the shifts that were scheduled.
- Correct the rows marked with the wrong hour type and let the amount payable recalculate.
- Close the period, save the file as support and file it together with the authorisations for those hours.
Tips and common mistakes
- Do not mix the ordinary hourly value with the monthly salary: the base of the premium is the hourly value, not the total earned.
- Type the quantity in hours and fractions, not in minutes; an hour and a half is written 1.5 and not 90.
- Check the hour type before paying, because a record marked as daytime when it was night changes the amount without anyone noticing.
- Set the multipliers once at the start of the period and not row by row, so that every record follows the same criterion.
- Do not leave half-finished rows: a row with hours and no hour type does not add up correctly in the summary.
- Keep the authorisation for the overtime hour; the record says how much is paid, but the support says why it was approved.
When to move to software
While overtime is occasional and a single person keeps the count, the template is more than enough and it teaches the logic of the premium before automating it. The move to software is justified when specific signals appear: there are several cost centres, there are shifts that cross midnight and the same hour has to be split into two types, payroll is already settled in a system and overtime arrives in a separate sheet that nobody reconciles, or the number of workers makes it impossible to review row by row.
At that point a system such as Kardex Tauro is the right step, because there the entry is born from the shift or from the clocking and the premium is calculated with the rules the company has configured. With Kardex Tauro the overtime sheet stops being a separate file and becomes part of the settlement of the period. The template remains useful for reviewing a particular month, for understanding how the amount payable is formed and for having a clear support when someone asks where a figure came from.
Download the template, set the multipliers to your own case and record the first hours of the period today. When a premium is written down with its type and its multiplier, the settlement stops being an argument and becomes a multiplication.
⬇ Download the template (Excel .xlsx)
