Calculate UK Working Days in Power Apps, Including Bank Holidays

A UK leave app needs more than a formula that removes Saturdays and Sundays. Bank holidays differ between England and Wales, Scotland, and Northern Ireland. Your organisation may also have shutdown days, local holidays, part-time patterns and half-day rules.

The reliable design is to store the applicable non-working dates as governed data, then let Power Fx count dates against that table. Do not bake a one-year holiday list into the app.

Decide the rules before writing the formula

Write down these decisions first:

Are the start and end dates both included?
Which UK division applies to each employee?
Are Saturday and Sunday always non-working days?
How are half days represented?
What happens if the end date is before the start date?
Who owns and verifies the holiday calendar?
Does a booking spanning two calendar years use both years' records?

This guide uses an inclusive date range and a Monday-to-Friday working week. Change the logic if your policy differs.

Create a holiday table

Use a SharePoint list, Dataverse table or another approved source with at least:

Column	Purpose
`HolidayDate`	Date only
`Division`	`england-and-wales`, `scotland` or `northern-ireland`
`Title`	Human-readable holiday name
`IsWorkingDay`	Optional override for local policy
`VerifiedOn`	Date the calendar was checked

The official GOV.UK Bank Holidays API publishes JSON for the three divisions through its bank-holidays endpoint. It is a source feed, not your entire leave policy. Copy or synchronise the dates into a controlled table, add company-specific non-working days, and have the appropriate owner verify them.

If you automate the import with Power Automate, validate the HTTP response and division before replacing existing records. Do not delete a working calendar because an external call returned an error or incomplete payload. HTTP and custom-connector actions may affect licensing, so check the current entitlements for the flow and its users.

Load the relevant dates

For a modest holiday table, load the employee's division into a local collection when the app starts or when the division changes:

ClearCollect(
    colHolidays,
    ShowColumns(
        Filter(
            CompanyHolidays,
            Division = varDivision
        ),
        HolidayDate,
        Title
    )
)

Check delegation warnings against your chosen data source. Filtering behaviour and supported operators vary by connector. A local collection is convenient for a small verified calendar, but collections do not automatically refresh when the source changes.

This example assumes Division is stored as text. If your source uses a SharePoint Choice column or another structured type, adjust the filter to the field shape shown by Power Apps and test its delegation behaviour.

Count inclusive working days

The following formula creates one row per calendar date, then keeps weekdays that are not in colHolidays:

With(
    {
        startDate: DateValue(dpStart.SelectedDate),
        endDate: DateValue(dpEnd.SelectedDate)
    },
    If(
        endDate < startDate,
        Blank(),
        CountRows(
            Filter(
                AddColumns(
                    Sequence(
                        DateDiff(startDate, endDate, TimeUnit.Days) + 1
                    ),
                    WorkDate,
                    DateAdd(startDate, Value - 1, TimeUnit.Days)
                ),
                Weekday(WorkDate, StartOfWeek.Monday) <= 5 &&
                IsBlank(
                    LookUp(
                        colHolidays,
                        HolidayDate = WorkDate
                    )
                )
            )
        )
    )
)

Microsoft documents that Sequence creates a single-column table of sequential numbers, while DateDiff and DateAdd calculate and generate the dates. Weekday can use Monday as the first day of the week.

Sequence has a documented maximum of 50,000 records. A normal holiday request should be nowhere near that boundary. If you are calculating long ranges, large schedules or many employees at once, do the calculation in a central service or data layer rather than generating large client-side tables.

Test the awkward dates

Use fixed examples with known expected results:

Monday to Friday with no holiday
Friday to Monday across a weekend
a bank holiday inside the range
a bank holiday on a weekend
a request crossing 31 December
Scotland and England using different dates
start and end on the same working day
end date before start date
a missing or stale holiday calendar

Test date-only values as date-only values. Converting date-times between UTC and local time can shift the apparent day, especially around daylight-saving changes.

Keep policy separate from calculation

The formula counts dates according to a defined rule. It does not decide entitlement, carry-over, part-time allocation, approval or payroll treatment. Keep those policy decisions explicit and test them separately.

Sources

Trying to make a business rule survive real users and real calendar exceptions? Join the Power Apps Builders Space for practical formula and data-design patterns.