The Hard Truth About Calculating Business Days in Power Apps

Most companies build leave-booking apps that fail on day one. They assume every week has seven working days. It never works.

You cannot let an employee book a Monday-to-Monday vacation and deduct eight days from their allowance. Weekends do not count. Public holidays do not count. You need a system that understands the actual business calendar.

Out-of-the-box Power Apps and Power Automate solutions do not handle this natively. You have to build the logic yourself. This guide focuses on the UK calendar, but the exact same principles apply anywhere in the world.

Here is how you fix the calculation.

Phase 1: Creating the Source of Truth

The first thing you need is a database of statutory leave days. We will use a simple SharePoint list.

Create a new SharePoint list in your Team site. Give it two columns: a Title and a Date. This will store the public holidays.

Now we need to populate it. Head to make.powerautomate.com and create a new Scheduled flow. Name it GetBankHolidays and set the recurrence to 1 month.

A quick tip on architecture. You should only write to SharePoint when absolutely necessary. As an enhancement, you can add a condition to check if the holiday data already exists before writing new rows. For now, we will focus on gathering the data.

Phase 2: The Premium Connector Trap

We need to pull live holiday data into Power Automate. To do this, we connect to an external website using an HTTP request.

You have two options. The first is the standard HTTP connector. Click New Step and choose HTTP.

But there is a catch. The standard HTTP action is a premium connector. If you have the licensing budget, simply choose the GET method and paste the official UK government URL: https://www.gov.uk/bank-holidays.json. Then skip ahead to the parsing step.

If you do not want to pay for a premium license, you need a custom connector workaround. Save your flow. We will come back to it.

Phase 3: Building a Custom Connector

Building a custom connector sounds intimidating. It is actually a straightforward process that completely opens up how you bring external data into Microsoft 365.

Go to the Data menu in make.powerautomate.com and click Custom Connections.

Click New Custom Connector.

Choose Create From Blank and name it HTTP Custom.

Set the Scheme to HTTPS. Set the Host to www.gov.uk. Set the base URL to a forward slash "/".

Click Security to move to step two. Select No Authentication. This keeps the API call simple.

Click Definition to move to step three. On the left side, choose New Action.

Fill out the Summary and Description. Give it a meaningful Operation ID. Power Automate uses this ID to identify the action later. Leave everything else as default.

Under the Request section, click Import from Sample.

Set the Verb to Get. Enter bank-holidays.json in the URL field. Click Import. Finally, click Create Connector at the top of the screen.

Phase 4: Testing the Connection

You built the framework. Now you need to prove it works.

Click the test link at the top of the screen. Click New Connection to generate an instance of your new setup. Then click Test operation.

If you configured it correctly, you will see a green tick under Operations.

Close the screen and return to Custom Connections. Click the plus icon next to your new connector to validate it.

Phase 5: Wiring It Up in Power Automate

Open your GetBankHolidays flow again. Add a new action and select Custom.

Choose your HTTP Custom connector and select the action you just built.

Phase 6: Parsing the JSON Data

The data you just pulled from the government website is raw JSON. It looks like a massive wall of text. Power Automate cannot read it yet. You need to slice it up using the ParseJSON action.

If you paste www.gov.tk/bank-holidays.json into a formatting tool like this, you can easily see the structure of the data.

Add the ParseJSON action to your flow.

Click the Content box and select the Body from your dynamic content menu.

Click Generate from sample. Paste the raw JSON data into the window to build your schema. Click Done.

Phase 7: Writing to SharePoint

Add a Create Item action and select your SharePoint list. Map the dynamic content to your columns.

When you select an element from the dynamic content, Power Automate will automatically wrap your action in an Apply to each loop. Do not panic. This happens because the system recognizes you are passing it a list of multiple dates.

Map the correct fields to your SharePoint columns.

Save and test your flow. Your SharePoint list will now populate with the correct bank holiday dates.

Phase 8: The Power Apps Formula

We have our data. Now we build the logic.

Open Make.PowerApps.com and edit your app. We need to load the holiday data into a collection the moment the app starts. Add this formula to the OnStart property:

ClearCollect(colBankHolidays, Filter('YourSharePointListName', DateDiff(Today(), Date) > 0))

Next we calculate the actual working days. We are heavily inspired by Mathew Devaney and his approach to calculating business days.

Place this formula into the Default property of your Leave Days control:

With({varDateRange: ForAll(Sequence(datLeaveTo.SelectedDate - datLeaveFrom.SelectedDate + 1), datLeaveFrom.SelectedDate + Value - 1)}, If(And(IsBlank(datLeaveFrom.SelectedDate), IsBlank(datLeaveTo.SelectedDate)), 0, CountIf(varDateRange, Weekday(Value) in [2, 3, 4, 5, 6])))

This formula creates a single-column table of the selected dates. It then counts only the days of a normal business week. In Power Apps, Sunday is day 1 and Saturday is day 7.

Now we subtract any dates that exist in our bank holiday collection. Add this condition to your logic:

Not(Value in colBankHolidays.Date)

Phase 9: Visual Validation

Your users need immediate visual feedback if they select an invalid date range.

Add this formula to the BorderColor property of your Start Date picker:

If(And(Or(Weekday(Self.SelectedDate) in [1,7], Self.SelectedDate in 'Bank Holidays UK'.Date, Self.SelectedDate > datLeaveTo.SelectedDate), !IsBlank(datLeaveFrom.SelectedDate), !IsBlank(datLeaveTo.SelectedDate)), Red, Parent.BorderColor)

Add this exact same logic to the BorderColor property of your End Date picker, just reversing the date check:

If(And(Or(Weekday(Self.SelectedDate) in [1,7], Self.SelectedDate in 'Bank Holidays UK'.Date, Self.SelectedDate < datLeaveTo.SelectedDate), !IsBlank(datLeaveFrom.SelectedDate), !IsBlank(datLeaveTo.SelectedDate)), Red, Parent.BorderColor)

Test the app by selecting dates that span a weekend and a bank holiday. Your Leave Days total will adjust automatically. If a user selects a bad date, the border turns red.

This entire methodology originated from a solution built by the Collab365 team. Massive credit to Eduardo Russo for laying the groundwork on the logic.

If you have questions on improving the build, drop a line to hello@collab365.com.