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 algorithm works like this:
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.
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
- Add one day at a time to our start date – let’s call this the running date.
- If the running date is not a weekend or a public holiday, increment a counter.
- Repeat until the counter equals the number of working days we need to add.
- 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.
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:
- Set runningDateIncremented to addDays(runningDate,1).
- Set runningDate to runningDateIncremented.
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