Calculating working days in Power Automate or Logic Apps

Note: there's currently an issue with images in Blogger. If you can't see the images in this post, you can read the article on the Chorus website instead.

Here’s a common scenario for any kind of SLA-driven process – calculate a target date or a due date for a task based on a number of working days. In most cases, we can take working days to be everything except weekends and public holidays. Let’s take a look at how you might go about it in Power Automate or Azure Logic Apps.

Note: the approach is identical regardless of whether you go with Power Automate or Logic Apps – I’m using Logic Apps in this case because the HTTP connector requires a premium license in Power Automate. 

The challenge

Given a start date and the number of working days we need to add, calculate a target date.

High-level approach

The algorithm works like this:
  1. Add one day at a time to our start date – let’s call this the running date.
  2. If the running date is not a weekend or a public holiday, increment a counter.
  3. Repeat until the counter equals the number of working days we need to add.
  4. Set the target date to the final value of the running date.

The trigger

To keep this as simple as possible, let’s start with a SharePoint list with three key fields – Start Date, Days to Add, and Target Date. We’ll manually set the Start Date and the Days to Add, and we’ll use the logic app to calculate the Target Date:

Note that those are UK date formats :-)

We'll trigger the Logic App every time an item is added to this list.

The variables

We need to define a few variables at the top:
  • workingDaysAdded is our counter – we’ll use this to keep track of how many qualifying days we’ve notched up in our loop.
  • runningDate is the date we’re going to add days to in our loop. We’re initially setting this to the start date we got from the SharePoint list item.
  • runningDateIncremented is a variable we’ll use to store temporary values when we’re calculating dates.

Note that we’re using strings for the date variables – Logic Apps and Power Automate connectors typically pass dates around as date strings in UTC format.

The public holiday data

You can get accurate public holiday data from various sources. In this case, I’m using an HTTP activity to send a GET request to https://www.gov.uk/bank-holidays.json, to get official public holiday data for the UK. This returns data that looks like this:

{
    "england-and-wales": {
        "division": "england-and-wales",
        "events": [
            {
                "title": "New Year’s Day",
                "date": "2015-01-01",
                "notes": "",
                "bunting": true
            },
            {
                "title": "Good Friday",
                "date": "2015-04-03",
                "notes": "",
                "bunting": false
            },
            {
                "title": "Easter Monday",
                "date": "2015-04-06",
                "notes": "",
                "bunting": true
            },
            ...
        ]
    },
    "scotland": {...},
    "northern-ireland": {...}
}

I’m then using a Parse JSON activity to convert that response into an object, to make it easier to work with later on:

Tip: That Use sample payload to generate schema option is very useful – just paste in a typical response from your web service and Logic Apps / Power Automate will do the work for you.

The loop

To do the calculation, we need a loop. In Logic Apps, I’m using the Until activity. In Power Automate it’s called Do Until, but it’s the same thing. We want to run the loop until the value of our counter, workingDaysAdded, is equal to the number of working days we need to add (provided by the SharePoint list item). At a high level, the activity looks like this:

For clarity, in advanced mode, the loop condition reads @equals(variables('workingDaysAdded'), triggerBody()?['DaystoAdd']).

The next thing we need to do is add one day to our running date. Logic Apps and Power Automate won’t let us write expressions like runningDate = addDays(runningDate,1), so we need to do this with two Set variable activities:
  1. Set runningDateIncremented to addDays(runningDate,1).
  2. Set runningDate to runningDateIncremented.
The end result – we’ve added one day to runningDate.

The weekday check

Checking whether a specific date is a weekday is straightforward in Logic Apps and Power Automate. We use the dayOfWeek function. This returns a number – 0 is Sunday, 6 is Saturday, and anything in between is a weekday:

If the condition is false, we do nothing and let the loop go back to the start. If the condition is true, we need to check whether our weekday is a public holiday.

The public holiday check

To check whether our weekday is a public holiday, we need to see whether it matches any of the records in our public holiday data. We can do this with a Filter array activity:



The From input is an array from our Parse JSON activity – specifically, this is:

body(‘Parse_JSON:_UK_public_holiday_data’)?[‘england-and-wales’]?[‘events’])

For the filter criteria, we’re returning only items where the date property matches the running date. We have to format the running date to match the format of the bank holiday data – in advanced mode, the filter array expression is:

@equals(item()?['date'], formatDateTime(variables('runningDate'), 'yyyy-MM-dd'))

If our filtered array contains more than zero items (use the length operator to check), we know our running date is a public holiday:

If this is true, we do nothing and let the loop go back to the start. If it’s false, we know that our running day is (1) not a weekend, and (2) not a public holiday. In this case, we increment our counter (workingDaysAdded) before the loop goes back to the start.

The result

When the workingDaysAdded counter reaches the target Days to Add value we pulled from the SharePoint list item, our loop will stop running. At this point, our running date is the date we need – it’s the start date with the required number of working days added. We can use an Update item activity to write this value back to the SharePoint list item:

If you check the SharePoint list against a public holiday calendar for England and Wales, you’ll see that everything adds up nicely!


Comments

Post a comment

Popular posts from this blog

Server-side activities have been updated

Image
If you use SharePoint Designer 2013 to build workflows, there's a fair chance you'll have come across the following error message: Server-side activities have been updated. You need to restart SharePoint Designer to use the updated version of activities.  Needless to say, restarting SharePoint Designer rarely makes the error go away. The usual advice is: Approach 1: Clear the cache folders See for example How to Clear Your SharePoint Designer 2010/2013 Cache . If you've just deployed some custom workflow activities to your site, this will probably solve your problem (and you should clear the cache folders every time you deploy custom activities). If the error occurs spontaneously, this approach often won't help. Approach 2: Reinstall SharePoint Designer This might work if you've got a preview version of SharePoint Designer installed. If not, it's unlikely to help. It didn't work for me, and it didn't work for countless others on the forums. Ap
Read more

The target principal name is incorrect. Cannot generate SSPI context.

Image
Today's problem occured after I restarted a Hyper-V based SharePoint 2013 farm (Windows Server 2012, one SharePoint 2013 machine, one SQL Server 2012 machine, one DC). I fired up Central Administration and was hit with the following error: Unknown SQL Exception 0 occurred. Additional error information from SQL Server is included below. The target principal name is incorrect. Cannot generate SSPI context. After checking the obvious things - testing connectivity to the DB server, checking the SQL service was running, verifying permissions, etc - I initially figured this was an issue with my Hyper-V snapshots being out of sync, so I ran the SharePoint Products Configuration Wizard. This hit me with the following error: Failed to detect if this server is joined to a server farm. Possible reasons for this failure could be that you no longer have appropriate permissions to the server farm, the database server hosting the server farm is unresponsive, the configuration database is
Read more

Custom Workflow Activity for Setting a SharePoint Group Owner

Image
In my last couple of posts I've looked at building custom workflow activities in Visual Studio 2012 to create sites and create groups in SharePoint 2013. In this post I'll walk you through how to build a workflow activity that changes the owner of a SharePoint group. This is slightly more challenging, as at the time of writing you cannot use the REST API to change the owner of a SharePoint group. Instead, you have to make a service call containing client-side object model (CSOM) XML. If I ever make it to San Diego, I owe  Chris Givens several drinks for helping me figure out this approach and switching me on to the capabilities of CSOM XML. Note: this is part of a series of posts on building workflow activities to manage sites, groups, users and permissions. For a complete list of posts, together with a more detailed walkthrough of how to build a custom workflow activity and make it available in SharePoint Designer, start at the beginning . Fundamentals You can't c
Read more