Holiday entitlement: practice exercises
Statutory holiday, joiners, pro-rata, holiday left, carry-over. 5 exercises on real-looking data, each marked the moment you press Check. The first exercise is below to try now; the full lesson is part of Pro.
Statutory holiday is 5.6 weeks (H1), capped at 28 days (H2). A week's holiday is the days a person works in a week. In C2, work out each person's statutory holiday in days.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Employee | Days per week | Statutory holiday | Weeks of holiday | 5.6 | |||
| 2 | Ollie | 1 | Cap (days) | 28 | ||||
| 3 | Rachel | 4.5 | ||||||
| 4 | Pippa | 2 | ||||||
| 5 | Ben | 3 | ||||||
| 6 | Jordan | 4.5 | ||||||
| 7 | Sam | 6 | ||||||
| 8 | Liam | 2.5 | ||||||
| 9 | Femi | 5 | ||||||
| 10 | Marek | 5 | ||||||
| 11 | Lucy | 2 |
How it works
In the UK, workers get 5.6 weeks of paid holiday a year, capped at 28 days. A week's holiday is the days someone works in a week, so MIN(days × 5.6, 28) gives the statutory days. Many employers give more.
Someone who joins part-way through the leave year gets a share of it. DATEDIF(start, end, "M") counts whole months, and CEILING(x, 0.5) rounds up to the next half day. MIN also sets a limit, such as the most days that can be carried over.
=MIN(B3*$H$1,$H$2) =DATEDIF(D3,$H$3,"M")+1 =CEILING(16.8*3/12,0.5) =MIN(C2-F2,5)
5.6 weeks, capped at 28 days
Statutory holiday is 5.6 weeks. A week's holiday is the days someone works in a week, so it's days × 5.6. It's capped at 28 days, and MIN applies the cap: =MIN(days*5.6, 28).
Worked example
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Name | Days a week | Statutory | Start | Months | This year | Weeks | 5.6 |
| 2 | Amira | 5 | 01/09/2025 | Cap | 28 | |||
| 3 | Ben | 3 | 14/01/2026 | Year ends | 31/03/2026 | |||
| 4 | Cara | 6 |
=MIN(B2*$H$1,$H$2)in C2. Ben works 3 days: 3 × 5.6 = 16.8. Cara works 6: 33.6 is over the cap, so MIN gives 28.=DATEDIF(D3,$H$3,"M")+1in E3. Ben started on 14 January. There are 2 whole months to 31 March, and the policy counts his first month in full: 3.=CEILING(16.8*3/12,0.5)in F3. 3 months is 3/12 of his 16.8 days: 4.2, rounded up to the next half day, 4.5.
What's left, and carrying over
- Days left to book = entitlement + carried over − taken − booked.
- A carry-over limit is another job for MIN:
=MIN(unused days, limit)gives the unused days, but never more than the limit.
What you'll practise
- Statutory holiday is 5.6 weeks (H1), capped at 28 days (H2). A week's holiday is the days a person works in a week. In C2, work out each person's statutory holiday in days.
- These people joined during the leave year, which ends on the date in H1. The policy counts the month someone starts as a full month. In C2, work out how many months of the leave year each person gets.
- Each person gets the share of their full-year holiday that matches the months they're here for. In D2, work out their holiday for this year, rounded up to the next half day.
- In F2, work out how many days each person has left to book: this year's entitlement plus any days carried over, minus the days already taken and the days booked.
- At the end of the leave year, people can carry over the days they haven't used, up to the limit in H1. In C2, work out how many days each person carries over.