Confident Cells

Holiday entitlement in Excel: part-time staff and starters

20 November 2026 · 2 min read

In the UK, most workers get 5.6 weeks of paid holiday a year, capped at 28 days. A week's holiday is the number of days someone works in a week, so the statutory days are =MIN(days per week × 5.6, 28). Someone working 3 days a week gets 16.8 days.

This guide shows the spreadsheet method for the standard rule. Your own policy may give more, and people with irregular hours or who work only part of the year have their own rules: check GOV.UK or your HR adviser for those.

Statutory holiday for each person

  1. Ben works 3 days a week. 3 × 5.6 = 16.8 days.
  2. Cara works 6 days a week. 6 × 5.6 = 33.6, but the cap is 28, so she gets 28 days.

Put 5.6 and 28 in their own cells, so the formula shows where the numbers come from, and lock them with $ (see $ and F4):

=MIN(B2*$G$1,$G$2)
ABCDEFG
1NameDays a weekHoliday (days)Weeks5.6
2Ada528Cap (days)28
3Ben316.8
4Cara628
5Dev2.514
MIN picks the smaller of days × 5.6 and the 28-day cap.

People who join part-way through the year

A starter gets a share of the year. With a leave year ending on 31 March, Ben (16.8 days a year) joins on 14 January. Counting his first month as a whole one, as many policies do, that's January, February and March: 3 months.

  1. Months: =DATEDIF(start,year end,"M")+1 gives 3. (More on DATEDIF in age and length of service.)
  2. Share: 16.8 × 3 ÷ 12 = 4.2 days.
  3. Many employers round up to the next half day: =CEILING(4.2,0.5) gives 4.5.

Days left to book

Entitlement plus days carried over, minus days taken and days already booked: =B2+C2-D2-E2. If carry-over is limited to, say, 5 days, =MIN(unused days,5) applies the limit.

Common mistakes

Forgetting the cap

Without MIN, someone working 6 days gets 33.6 days of statutory holiday. Statutory holiday never goes above 28 days, though a contract can give more.

Using 20 or 28 days for everyone

Part-time staff get the same 5.6 weeks, but their week is shorter. Always start from days worked per week.

Bank holidays counted twice

Bank holidays can be part of the 28 days. Decide whether your figure includes them, and say so in a heading.

Try it now

Your turn

Statutory holiday is 5.6 weeks (G1), capped at 28 days (G2). In C2, work out each person's statutory holiday in days. Your formula will be copied down to C7.

ABCDEFG
1NameDays a weekHoliday (days)Weeks5.6
2Ollie1Cap (days)28
3Rachel4.5
4Pippa2
5Sam6
6Jordan5
7Lucy3.5