What it's for
- Small teams that track leave in a spreadsheet.
- Managers checking who is away before a launch or a busy month.
- Teams spread across states (AU) or nations (UK) with different public holidays.
What's inside
- How to use
- Instructions, the colour key and the calendar codes.
- Settings
- When your leave year starts. The UK edition also asks whether bank holidays come out of the allowance.
- Team
- Each person, their public holiday region, their entitlement for the year and anything carried over.
- Leave log
- One row per booking: who, the type of leave, first and last day, and half day or not. Working days are counted for you.
- Calendar
- Pick a month to see who is away each day, with public holidays and weekends shaded.
- Balances
- Taken or booked, and what is left, for each person and leave type.
- Public holidays
- 2027 state (AU) or nation (UK) holidays from the official sources, with room to add your own.
Preview
| Person | Region | Leave | Dates | Days counted | Annual leave left |
|---|---|---|---|---|---|
| Alex | NSW | Annual leave | Mon 22 Mar to Fri 2 Apr 2027 | 8 | 20 + 3 carried over − 8 = 15 |
| Alex | NSW | Personal/carer's leave | Mon 12 Apr 2027, half day | 0.5 | Personal leave: 10 − 0.5 = 9.5 |
| Jordan | VIC | Annual leave | Mon 7 to Fri 11 Jun 2027 (works 3 days a week) | 3 (typed in) | 12 − 3 = 9 |
Alex's two weeks cover 10 weekdays, but Good Friday (26 March) and Easter Monday (29 March) are NSW public holidays, so 8 days are counted. Jordan works three days a week, so the 3 is typed into "Days if different".
How to use it
- Download your edition and open it in Excel, or in Google Sheets with File > Import.
- On Settings, type the first day of your leave year. It can start in any month.
- On Team, add each person with their public holiday region, their entitlement for this leave year in days, and any days carried over. You type the entitlement: it depends on the person's hours, award, agreement or contract.
- In the Leave log, add each booking. Leave "Last day" blank for a single day. "Half day?" works on one-day bookings, so a week ending in a half day is two rows: the full days, then the half day.
- For someone who doesn't work Monday to Friday, type their actual working days in "Days if different".
- Check Balances, and pick a month on Calendar to see who is away.
The formulas, explained
- Days counted
-
=(NETWORKDAYS(C4,I4)-COUNTIFS('Public holidays'!$A$4:$A$300,region,'Public holidays'!$B$4:$B$300,">="&C4,'Public holidays'!$B$4:$B$300,"<="&I4,'Public holidays'!$D$4:$D$300,1))*IF(AND(E4="Yes",C4=I4),0.5,1)Counts Monday to Friday between the first and last day, takes off the public holidays in the person's region that fall on a weekday, and halves a one-day booking marked as a half day. "Days if different" overrides it. With no region on Team, no holidays are taken off.
- Annual leave taken
-
=SUMIFS('Leave log'!$G$4:$G$503,'Leave log'!$A$4:$A$503,$A4,'Leave log'!$B$4:$B$503,"Annual leave",'Leave log'!$C$4:$C$503,">="&Settings!$B$3,'Leave log'!$C$4:$C$503,"<="&Settings!$B$4)Adds up the person's annual leave that starts inside the leave year.
- Remaining
-
=B4+C4-D4Entitlement + carried over − taken or booked. It turns red below zero.
- Bank holidays deducted (UK edition)
-
=COUNTIFS(region,Team!B4,date,">="&Settings!$B$3,date,"<="&Settings!$B$4,weekday,1)When bank holidays come out of the allowance, this counts the bank holidays in the person's nation that fall on weekdays in the leave year.
Every formula uses functions that Excel, Google Sheets and LibreOffice all have. There are no macros or scripts.
Country notes
Australia
Full-time and part-time employees get 4 weeks of paid annual leave a year under the National Employment Standards, based on their ordinary hours; some shiftworkers get more. Casual employees don't get paid annual leave. Unused annual leave rolls over from year to year. Paid sick and carer's leave is 10 days a year for full-time employees and pro rata for part-time employees (1/26 of ordinary hours), and it builds up from year to year too. Long service leave mostly comes from state and territory laws, so the planner keeps it as a balance only. Every employee, including casuals, can take 10 days of paid family and domestic violence leave each year. Keep that out of this shared file: Fair Work says pay slips must not mention it, to reduce the risk to the employee's safety.
United Kingdom
Almost all workers are entitled to 5.6 weeks' paid holiday a year. For a 5-day week that is 28 days, and statutory paid holiday is capped at 28 days. Part-time workers get 5.6 times the days they work each week: 16.8 days for 3 days a week. Bank holidays don't have to be given as paid leave, and an employer can include them in the statutory entitlement; the UK edition's setting handles both. For leave years starting on or after 1 April 2024, irregular-hours and part-year workers build up holiday at 12.07% of the hours they work in each pay period. Use GOV.UK's holiday entitlement calculator for those. From 6 April 2026, employers must keep annual leave and holiday pay records for at least 6 years.
Each statement comes from the official sources at the end of this page, checked 4 October 2026.
Questions
How much annual leave do employees get?
In Australia, full-time and part-time employees get 4 weeks a year, based on their ordinary hours (more for some shiftworkers). In the UK, almost all workers get 5.6 weeks, which is 28 days for a 5-day week. Awards, agreements and contracts can give more. The planner doesn't fill entitlements in: you type each person's.
How do I work out part-time leave?
In the UK, multiply the days worked each week by 5.6: 3 days a week gives 16.8 days. In Australia, 4 weeks of a part-timer's ordinary hours is the same as 4 of their working weeks: 12 days for someone who works 3 days a week. Fair Work's and GOV.UK's calculators (linked below) do the sums for you. When a part-timer books leave, type their actual working days in "Days if different".
Do bank holidays count?
In the UK an employer can include bank holidays in the 5.6 weeks or give them on top. Set "Bank holidays come out of the holiday allowance?" to Yes or No. Either way, bank holidays inside a booking aren't counted as days of leave.
How do I track half days?
Add the half day as its own one-day row and choose Yes under "Half day?". It counts as 0.5. For a week that ends with a half day, add the full days and the half day as two rows.
Which public holidays are filled in?
Official 2027 state-wide (AU) or nation-wide (UK) holidays, checked against each government's list on the date below, plus the early-2028 ones already published. Part-day and local holidays aren't included, and nor is Victoria's Friday before the AFL Grand Final, which wasn't confirmed for 2027 when we checked. Add other dates in the empty rows of the Public holidays sheet.
When should I move to software?
When more than one person updates the file, when you need approvals, or when balances have to accrue every pay period. That's when a shared spreadsheet starts to go out of date.
Sources
Last reviewed 4 October 2026. We link to the regulator, the law or the platform's own help pages.
- Fair Work Ombudsman: annual leave
- Fair Work Ombudsman: paid sick and carer's leave
- Fair Work Ombudsman: long service leave
- Fair Work Ombudsman: family and domestic violence leave
- Fair Work Ombudsman: pay slips (family and domestic violence leave)
- Fair Work Ombudsman: leave calculator
- GOV.UK: holiday entitlement
- GOV.UK: calculate leave entitlement (12.07%)
- GOV.UK: holiday entitlement calculator
- GOV.UK: UK bank holidays
- Acas: Employment Rights Act 2025 (holiday records)
- Public holiday dates: the state and territory government pages listed in the file's Public holidays sheet
Changes: 4 October 2026: first published.
Free to use and change in your own business. Please don't resell the files or publish them as your own. More free templates.