Timesheet template in Excel: hours worked
Timesheet template in Excel: hours worked
When the hours of a team are counted from memory at the end of the week, the result does not survive a review: someone remembers staying two hours longer on Thursday, nobody can find those hours anywhere, and the payment closes with an approximate figure. That disorder is not a problem of trust but of record keeping: if the hours are not written down the same day, they do not exist.
This timesheet template in Excel is the hours sheet of the team. It holds two hundred records with the date, the employee, the category, the start time and the end time, and it works out the hours worked and the amount to pay from the hourly rate you type in the header. At the bottom a summary shows the records, the total hours, the overtime hours, the total amount and the average hours per record.
The axis of this format is the clock, not attendance. What it answers is how long each person worked and what that time is worth; whether they showed up is another question and it has a format of its own. Keeping that difference clear is what stops two separate controls from overlapping or one of them from being left undone.
⬇ Download the template (Excel .xlsx)What a timesheet is and who it is for
A timesheet is the daily record of the time each person gives to the business. It is filled in with the real clock: start time, end time and, when they happen, overtime hours. Its purpose is twofold. On one side it feeds payroll with a figure that can be verified, so the payment comes out of a record and not out of a verbal agreement. On the other it shows how the time is distributed: which days accumulate hours and when the team works beyond its shift.
It is for the owner of a small business who pays by day or by hour, for the administrator who prepares payroll every month and needs a basis for the calculation, and for the area supervisor who organises shifts and wants to measure the real workload of the team. It is also, although this is rarely said out loud, for the employee: a record filled in on the same day protects that person from doubt and lets them check that all their hours arrived in the payment.
It does not ask for payroll or accounting knowledge: the white columns are typed by hand — date, name, category, start and end — and the grey columns do the arithmetic. The only technical decision is the hourly rate, and that figure is typed once, in the header.
Why attendance tracking is not enough
Attendance and hours are two different controls and they are often confused. An attendance tracker answers a binary question with a reason: who came, who did not and why — excused absence, unexcused absence, leave or holidays. Its unit of measure is the day, and its value lies in counting days and justifying the missing ones. A timesheet answers a question of magnitude: how much time was worked and what it is worth in money. Its unit of measure is the hour.
When the two are mixed into a single file, the result is a hybrid that serves neither purpose well: it does not settle payroll with precision, and it does not make clear how many days someone missed. That is why this format concentrates on hours and leaves attendance statuses out. The right way to use them is side by side, crossed at the close of the period: a day marked as an absence cannot carry hours, and a record with hours is proof that there was work that day.
What the template includes
The file is an Excel workbook with the hours sheet and an instructions sheet. There are no macros and no add-ins: just cells to type in, drop-down lists and simple formulas. This is what you find inside:
| Component | What it is for |
|---|---|
| Room for 200 records | Two hundred rows to load one row per day and per person; enough for several months of a small team. |
| Rate header | Two editable cells: the hourly rate and the overtime multiplier. They are typed once and feed the whole column. |
| Hours worked column, automatic | Subtracts the start time from the end time and turns the result into decimal hours. |
| Amount to pay column, automatic | Multiplies the hours by the hourly rate and adds the overtime hours at their multiplier. |
| Category drop-down list | Classifies each record by type of contract: staff, temporary, trainee or other. |
| Sheet totals | Adds up the hours worked, the overtime hours and the amount to pay of every record at the foot of the sheet. |
| Period summary | Shows the number of records, the total hours, the overtime hours, the total amount to pay and the average hours per record. |
The columns on the sheet
Each column has a job and it is worth respecting it. The white ones are typed in; the grey ones hold the formula and should not be touched. These are the columns, in order:
| Column | What it is for |
|---|---|
| Date | The day worked; it lets you sort the period and know which week each hour belongs to. |
| Employee | One row per person and per day. Write the full name and always in the same way, so the totals can be added up per worker. |
| Category | Chosen from the list — staff, temporary, trainee or other — and used to split the cost by type of contract. |
| Start time | The exact time the person began, in twenty-four hour format. |
| End time | The exact time the person finished, also in twenty-four hour format. |
| Hours worked | Calculated by the sheet: the end time minus the start time, expressed in decimal hours. |
| Overtime hours | Only what was worked beyond the shift; when there was none, the cell is left empty. |
| Amount to pay | Calculated by the sheet: the normal hours at the hourly rate, plus the overtime hours at their multiplier. |
| Notes | Short remarks: a half day of leave, a shift change, the place where the person worked, anything unusual about the day. |
How the hours and the amount to pay are calculated
The calculation takes two steps and neither is done by hand. First, the sheet subtracts the start time from the end time and turns that difference — which in Excel is a moment of the day — into decimal hours. Second, it multiplies those hours by the hourly rate written in the header and adds, when there are any, the overtime hours at their factor. If the hourly rate changes, you correct that cell and the whole column updates.
Let us look at the example with figures. A person with the staff category clocks in at 08:00 and clocks out at 17:00: the sheet shows nine hours worked. With the hourly rate at 6,000 and no overtime, the amount to pay is 54,000. If that same person had worked one extra hour, the record would read ten hours and the pay would rise to 61,500, because the overtime hour is valued with the multiplier in the header. And if the period holds two records like the first one, the summary shows two records, eighteen hours and a total of 108,000.
| Item | Value in the example |
|---|---|
| Start time | 08:00 |
| End time | 17:00 |
| Hours worked, automatic | 9 |
| Hourly rate in the header | 6,000 |
| Overtime hours | None |
| Amount to pay, automatic | 54,000 |
| The same day with one overtime hour | 10 hours and 61,500 |
| Two records like the first one | Records: 2 · Hours: 18 · Total: 108,000 |
Read the full result: nine hours a day at 6,000 is 54,000. The period summary does not add up days or attendance, it adds up hours and money, and that is the figure payroll is prepared from.
How to use it, step by step
The first time it takes a few minutes to set up. This is the order to follow:
- Open the file, type the hourly rate in the header cell and adjust the overtime multiplier if your agreement differs from the one the sheet carries.
- Load the team basics into the sheet: the full name of each person and their category, chosen from the list, so the records stay consistent.
- Enter the date, the employee and the start and end times every day, in twenty-four hour format and always with the same convention.
- Write the overtime hours only when they were really worked beyond the shift; when there were none, leave the cell empty.
- At the end of the week, read the grey columns and the summary; if a figure does not add up, look for the badly written record before you touch the formula.
- Before paying, compare the totals with the attendance tracker for the same period and keep a copy of the closed month as support.
Tips and common mistakes
Almost every mismatch in an hours sheet comes from the same cause: a figure written in a way the sheet cannot read. These are the most frequent slips:
- Typing an afternoon time as if it were a morning time. If five in the afternoon is entered as 05:00 instead of 17:00, the subtraction returns a negative number and the mistake spreads into the totals.
- Writing the hours as text, in words or with an h after the number, instead of the start time and the end time. The sheet needs both times to subtract them.
- Typing over the grey columns. Those cells hold the formula; if a number replaces it, the record stops recalculating.
- Leaving the end time empty when the period is closed. The calculation ignores incomplete rows, so those hours are neither counted nor paid.
- Recording hours on a day marked as an absence in the attendance tracker. It is the most frequent inconsistency when payroll closes.
- Forgetting shifts that cross midnight. If the person started at 22:00 and finished at 06:00 the next day, a plain subtraction does not tell one day from the next, and the record needs its own note.
When to move to software
For a small team this template is enough: it is free, it is understood in one afternoon and it puts in order a figure that used to be remembered. Its limit appears with volume and with the number of hands that use it. When there are many employees, when people join and leave during the month, when several people fill in the register at different times, or when the hourly rate changes by category, the sheet starts to fall short and copying data between files opens the door to mistakes.
At that point a system helps, and we say it without exaggeration: Kardex Tauro is inventory and movement control software, and it does not replace this hours sheet or decide how the time of your team is paid. What it does, when the business already needs it, is keep the movements of the day in one place, so that the hours figure and the rest of the operations do not live in separate files that nobody crosses.
Download the timesheet template in Excel for free, type the hourly rate in the header and fill it in on the same day, not at the end of the month. Remember that it is an internal control tool: it records and values the time worked, and it does not replace a payslip or an official settlement.
⬇ Download the template (Excel .xlsx)
