Free Excel template to track your team’s hours and holidays
A ready-to-fill Excel workbook that works out the four balances of each employee under the Swiss L-GAV (CCNT): hours, holidays, public holidays and rest days. You type the month’s hours and the absences, it does the rest.
Balances at end of September 2026
Café de la Place (example). Everything is calculated, nothing to enter here.
| Employee | Hours | Holidays |
|---|---|---|
| Camille Rochat | +45.45 h | 9.25 d |
| Luca Moretti | +205.80 h | 14.75 d |
| Inès Favre | −1.46 h | 9.33 d |
| Thomas Keller | −1.66 h | 12.25 d |
| Sofia Pereira | +9.50 h | 5.33 d |
| Team total | +257.63 h | 50.92 d |
What the Excel template contains
The workbook comes down to five useful tabs. Three are filled in, two give the answers.
- 1 Team: your employees, their workload, their contract dates and what each of them had at the start.
- 2 Hours: the total hours worked per person, one figure a month.
- 3 Absences: holidays, public holidays, sickness and other leave, one line per absence.
- Balances: the team dashboard, with the four balances of each person and the points to watch.
- Statement: one employee’s statement, month by month, ready to print and have signed.
Two optional tabs complete it: Corrections, for hours paid out with the salary or a rest day that was not granted, and Settings, where the L-GAV entitlements are already in place. No sheet is protected and the file contains no macros.
How to use the spreadsheet in three steps
The file is built for a manager who does not have an hour to give it. Only the tinted cells are filled in, everything else is calculated.
- Enter your team. One line per person. The name is enough: without a workload the file counts full time, and the dates and opening balances are optional.
- Carry over the month’s hours. One figure per employee, taken from your timesheets or your time clock, in decimal hours: 8 h 30 is written 8.5. A month left empty is not counted.
- Note the absences. One line per absence, from the first day to the last, rest days included. Absences already planned can be entered in advance.
The Balances tab stops by itself at the last month that has hours, and the individual statement follows. To start mid-year, enter the date tracking begins and what each person had on that day.
How the four balances are calculated under the L-GAV
The template applies the national collective labour agreement for hotels, restaurants and cafés (L-GAV, CCNT in French), and the method of the official working time control form.
| Balance | Rule applied | L-GAV |
|---|---|---|
| Hours | Hours owed = (calendar days − absence days) ÷ 7 × the contract’s weekly hours. The balance is the gap between hours worked and hours owed. | Art. 15 |
| Holidays | 5 weeks a year, which is 35 calendar days or 2.92 days a month. A week of holiday uses 7 days. | Art. 17 |
| Public holidays | 6 paid days a year, half a day a month, earned during holidays too. | Art. 18 |
| Rest days | 2 days a week. The file assumes they were granted and counts the ones still owed. | Art. 16 |
An example: at 42 hours a week, March has 31 calendar days and is worth 186 hours owed. A week of holiday removes 7 days, which is 42 hours. For part time, the weekly hours follow the workload: 33.6 hours at 80%.
Full-time weekly hours are set in the file: 42 hours as a rule, 43.5 hours in seasonal establishments and 45 hours in small establishments.
Why track these balances every month
Because the agreement asks for it. The employer keeps a record of hours worked and rest days actually taken, and the employee may ask at any time about their hours and the rest days, public holidays and holidays they still have (art. 21).
Because it costs less. Overtime is paid at 100% of gross salary when working time is recorded, the balance is given to the employee in writing every month, and it is paid at the latest with the last salary. If one of those conditions is missing, it is 125% (art. 15).
Because a balance that grows quietly ends up being paid all at once. Above 200 hours at the end of a month, the excess must be paid with the following month’s salary. At the end of the contract, each remaining holiday is worth 1/30 of the gross monthly salary, and each public holiday or rest day not taken 1/22.
That is what the template’s individual statement is for: one statement per person, to hand over and have signed every month.
The limits of an Excel spreadsheet
A spreadsheet is enough to get started, and for a small team it can be enough for a long time. It still has limits worth knowing before relying on it.
- Everything is typed twice. The hours come from somewhere else, a paper sheet or a time clock, and every copy is a chance to get it wrong.
- One person holds the file. The employee only sees their balance the day you print their statement.
- An overwritten formula gives no warning. The file is unprotected to stay simple, and a calculated cell replaced by a number falsifies the result silently.
- It covers one calendar year, and a change of workload mid-year needs a second line.
- It does not know the schedule: a rest day that was not granted is noted by hand.
What about the team schedule?
This template tracks balances, not the schedule. If you are looking for an Excel staff schedule template, know that scheduling is precisely what a spreadsheet does worst: it tells nobody when a shift changes, handles neither swaps nor time off requests, and everyone ends up with their own version of the file.
We compared the two approaches in Shifta or Excel for scheduling, and gathered the best staff scheduling software for a Swiss venue.
When the balances calculate themselves
Shifta, the software behind this blog, keeps these four balances with nothing typed twice. The hours come from the time clock or from the team’s own entries, the absences from approved requests, and every balance is explained line by line.
| What changes | Excel template | Shifta |
|---|---|---|
| Hours worked | Carried over by hand each month | From the time clock or the team’s own entries |
| Absences | Entered by the manager | Requested by the team, approved in one tap |
| Balances | Up to date when the file is | Always up to date, with the detail of every line |
| Schedule | Not covered | Schedule, swaps and cover |
| Price | Free | Free 14-day trial, then a subscription |
Official sources
- L-GAV for hotels, restaurants and cafés (official text), art. 15 to 18 and 21
- GastroSuisse, labour law and the L-GAV
This template and this article apply the general rules of the L-GAV (as of 1 January 2017, 2nd edition 2021) and are not legal advice. Contracts and individual situations can change the calculation: the official text, linked above, prevails.
Frequently asked questions
Is the Excel template really free?
Yes. You receive it by email, as an attachment, with no card and no account to create. Two files are attached: the empty template, and the same one with an example team so you can see the result.
Which languages does the workbook exist in?
English, French and German. From this page you receive the English one, with its tabs, labels and help messages in English. The calculation is the same in all three.
Which software does the spreadsheet work with?
It is a workbook in .xlsx format, with no macros and no protection, built and checked in Microsoft Excel. It only uses common functions, but we have not tested it in other spreadsheet software.
Do hours have to be entered day by day?
No. One total per person per month is enough, taken from your timesheets or your time clock. A month left empty is not counted, so it does not show a negative balance.
Does the template work for part-time staff?
Yes. Enter the workload and the hours owed follow: 33.6 hours a week at 80%, for example.
Can I start in the middle of the year?
Yes. Enter the date tracking begins, and what each person had on that date as opening balances. The file counts from that day.
What does a negative hours balance mean?
That the person worked fewer hours than were owed over the period: those hours are still to be worked. A positive balance is to be compensated with time off or paid.
What do you do with my email address?
It is used to send you the template, and we may contact you again about Shifta. You can stop that at any time by replying “stop” to any of our emails.
The Excel template, free and ready to fill in
Your team’s hours, holidays, public holidays and rest days, calculated under the L-GAV.
- The four L-GAV balances, calculated automatically
- Three tabs to fill in, and a name is enough to start
- An individual statement to print and have signed each month
- An Excel file (.xlsx) with no macros and no password
Related articles
Excel got you this far. Here's where it breaks
A spreadsheet warns nobody, checks nothing, and goes stale the moment it's printed. An honest look at what Excel still does well, and what it never will.
The best staff scheduling software in 2026
Seven tools compared for a Swiss venue, one price actually checked, and the three questions that settle it faster than any feature list.
Handling last-minute absences without spending an hour on your phone
A text at 16h20, a server off sick. The real cost is invisible: forty minutes lost on the phone, and a replacement nobody records.