Capacity planner that counts leave and public holidays

What it's for

• Agencies planning who works on what over the next quarter.
• Spotting over-booked weeks before they happen.
• Seeing the effect of leave and public holidays on delivery.

What's inside

Settings

The first Monday of your 12 weeks.

Team

Each person's hours a week, days worked a week and billable target.

Public holidays

Your public holidays, from your government's list.

Leave

Who is away and when. Leave days in each week are counted for you.

Allocation

Planned hours per person, per project, per week.

Capacity

Available hours, allocated hours and utilisation for each person and the team, week by week.

Preview

Worked example: the week of Monday 8 March 2027, with a public holiday that Monday

In the week before, with no holiday or leave, Alex has 38 hours available and 30 allocated (79%), and Jordan 22.8 available and 10 allocated (44%, below a 60% target, so amber).

How to use it

1. On Settings, type the first Monday you want to plan from.
2. On Team, add each person with their hours a week, the days they work each week and, if you use one, a billable target.
3. Add your public holidays and everyone's leave. Leave "Last day" blank for a single day.
4. On Allocation, add a row for each person and project, with planned hours for each week.
5. Read Capacity: red means more hours allocated than available, amber means below the person's target.

The formulas, explained

Leave days in a week

=NETWORKDAYS(MAX($B4,G$3),MIN($F4,G$3+4),'Public holidays'!$A$4:$A$60)

Counts the weekdays of a booking that fall inside that week, not counting public holidays. A half day counts half.

Available hours

=hours a week × (1 − (public holidays + leave days) ÷ days worked a week)

Each day off takes away one working day's share of the week's hours.

Utilisation

=IF(N(C5)=0,"",C38/C5)

Allocated hours ÷ available hours.

Every formula uses functions that Excel, Google Sheets and LibreOffice all have. There are no macros or scripts.

Questions