How to Exclude Bank Holidays in Excel

Published 2026-04-27 · Updated 2026-04-27

When to use this formula

Use it when you need a repeatable spreadsheet calculation for invoice due dates, project plans, payroll checks, support SLAs, or other weekday-based deadlines. The holidays argument transforms a basic weekday count into an accurate business-day calculation that reflects real-world closures.

Step 1: Build your holiday list

Create a separate column or sheet with every public holiday and company closure date your organisation observes. For UK users, include at least the 8 permanent bank holidays for England and Wales. Store them as proper Excel dates, not text.

Example list for 2026 (England & Wales):

Step 2: Reference the range in your formula

Use an absolute reference (dollar signs) so the holiday range stays fixed when you copy the formula down a column:

=NETWORKDAYS(A2, B2, $H$2:$H$20)

The dollar signs lock the reference to H2:H20. Without them, copying the formula to row 3 would shift the holiday range to H3:H21, which would miss the first holiday.

Step 3: Test with a month that has bank holidays

May 2026 is the best test case because it has two bank holidays. Without the holidays argument, NETWORKDAYS returns 21 working days for May. With the holiday list, it correctly returns 19. If you see 21, your holiday range is not being referenced correctly.

Common checks

• Make sure your date cells are real dates, not text. Use the DATE function or format cells as Date.
• Keep public holidays in a separate list with absolute references so you can reuse it.
• Confirm whether the start date should count per your policy.
• Update your holiday list each year — some bank holidays shift (Easter, substitute days).