Confident Cells

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.

Your turn

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.

ABCDEFGH
1EmployeeDays per weekStatutory holidayWeeks of holiday5.6
2Ollie1Cap (days)28
3Rachel4.5
4Pippa2
5Ben3
6Jordan4.5
7Sam6
8Liam2.5
9Femi5
10Marek5
11Lucy2

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

ABCDEFGH
1NameDays a weekStatutoryStartMonthsThis yearWeeks5.6
2Amira501/09/2025Cap28
3Ben314/01/2026Year ends31/03/2026
4Cara6
  1. =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.
  2. =DATEDIF(D3,$H$3,"M")+1 in E3. Ben started on 14 January. There are 2 whole months to 31 March, and the policy counts his first month in full: 3.
  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

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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.