In short
The leave tracking spreadsheet calculates each employee's annual leave by seniority and age under Art. 53 of the Labour Law, counts recorded leave in working days after excluding weekends and public holidays, and shows the remaining balance. It is a good start for small teams, but shifted entitlement dates, the approval process and employee signatures still have to be tracked by hand.
Sheets in the spreadsheet
Employees
One row per employee. Grey columns are formulas; fill in the white columns only.
- Full name
- Start date
- Date of birth (optional)
- Underground work (Y/N)
- Completed years of service
- Last entitlement date
- Entitlement this period (days)
- Carried over (days)
- Used this period (days)
- Remaining (days)
- Next entitlement date
Leave records
One row per leave. Working days are calculated excluding weekends and public holidays; eves count as half days.
- Full name
- Leave type
- Start
- End
- Working days (automatic)
- Adjustment (days)
- Deducted from annual leave (days)
- Notes
Public holidays
Full-day holidays and half-day eves, from İzin360's public holiday data. You can add the next year's holidays in the rows below.
- Date
- Holiday
- Half day (eve)
- Eve
Settings
Working week: Monday–Friday or Monday–Saturday. Working days are calculated according to this choice.
- Setting
- Value
How to use
Steps, the assumptions behind the formulas and what must be tracked by hand.
- Step
- Description
How to fill it in
- On the Employees sheet, enter each employee's full name, start date and optionally date of birth; enter Y for underground work.
- Enter leave left over from earlier periods in the "carried over" column; annual leave does not expire and unused days accumulate.
- Record each leave on the Leave records sheet, choosing the name and leave type from the lists. The working days column excludes weekends and public holidays automatically.
- If you work Monday to Saturday, change the working week on the Settings sheet; working days are calculated accordingly.
- If there was an absence that does not count, such as unpaid leave or a long medical leave, move the next entitlement date forward by hand; the spreadsheet does not calculate this shift.
Legal basis and points to note
- Annual leave: at least 14 days for 1–5 years (5 years included), 20 days for more than 5 and less than 15 years, 26 days for 15 years or more; at least 20 days for employees aged 18 or under or 50 and over; +4 days for underground work (Labour Law, Art. 53). The spreadsheet applies these rules.
- Weekly rest days and public holidays within the leave are not counted as leave (Art. 56); the spreadsheet excludes them from working days.
- Absences other than those listed in the law (for example unpaid leave) move the entitlement date back (Art. 54); the spreadsheet does not calculate this and it must be corrected by hand.
- The spreadsheet does not replace the signed leave record that employers must keep (Art. 56; Regulation, Art. 20).
Frequently asked questions
How does the spreadsheet calculate annual leave?
It gives 14, 20 or 26 days based on completed years of service since the start date; if a date of birth is entered, it applies at least 20 days when the employee was 18 or under or 50 or over on the last entitlement date, and adds 4 days for underground work (Labour Law, Art. 53).
Are public holidays and weekends excluded?
Yes. The working days column uses NETWORKDAYS.INTL to exclude weekends and the full-day holidays on the Public holidays sheet; half-day holidays such as eves reduce the count by half a day when they fall within the leave.
Does it work in Google Sheets?
Yes. The formulas used (DATEDIF, EDATE, NETWORKDAYS.INTL, SUMIFS, SUMPRODUCT) exist in both Excel and Google Sheets. Upload the file to Google Drive and open it with Sheets.
How do I move from the spreadsheet to İzin360?
In İzin360 you import employees in bulk with an Excel template; you can copy names and start dates from this spreadsheet. Enter carried-over leave as opening balances on each employee's page; entitlement and date shifts are then calculated automatically.
Sources
- Labour Law No. 4857, Arts. 53, 54, 56
- Annual Paid Leave Regulation, Art. 20