In this post I will show you a Power Automate Flow you can use to add days to a given date and exclude weekends, or specific dates from the result. I was inspired to do my own solution by some recent posts by others:
Tom Riha – How to add only working days to a date using Power Automate
Matthew Devaney – Power Apps Calculate Business Days Excluding Weekends & Holidays
Both of these guys have brilliant blogs and I highly recommend subscribing to both of them. It is really interesting to see how we all approached the problem slightly differently.
Power Automate includes the useful expression addDays which will add a specified number of days to a date that you provide. While this function is useful, it does not allow you to exclude certain days. Such as weekends or public holidays.
Basic Flow – Add days excluding weekends
First, add two compose actions, name the first one Date and the second one DaysToAdd.
Next we will generate a range of dates for the next 365 days and also what day of the week they represent. Add a Select action and name it Dates:
The expressions used in the select:
From: range(0,365)
Date: formatDateTime(addDays(outputs('Date'),item()), 'yyyy-MM-dd')
Day: dayOfWeek(addDays(outputs('Date'),item()))
A sample of the output of the select action will be a range of 365 dates, something like this:
[
{
"Date": "2022-04-04",
"Day": 1
},
{
"Date": "2022-04-05",
"Day": 2
},
{
"Date": "2022-04-06",
"Day": 3
},
{
"Date": "2022-04-07",
"Day": 4
},
{
"Date": "2022-04-08",
"Day": 5
},
{
"Date": "2022-04-09",
"Day": 6
}
]
As you can see, we are generating an array of dates, and the day of the week that date represents (0 being Sunday and 6 being Saturday).
Next, add a Filter Array step, to reduce the dates array so that it only includes week days:
The expression used in the Filter is:
@and(not(equals(item()['day'], 0)), not(equals(item()['day'], 6)))
Now we have a date array that contains only weekdays, we just need to pick out our target date. Add a compose action called TargetDate:
The expression used here is:
body('Filter_array')[outputs('DaysToAdd')]['date']
This expression will return just the date that we are looking to find, here is the output from my example:
The TargetDate compose action is optional, you can simply use the expression shown in any of your actions.
Complete Code for Basic Example
If you prefer, you can simply copy the code below into your clipboard and paste it into your own flow:
{
"id": "df633c17-f74d-48fa-bd16-e67fbcc8eeef",
"brandColor": "#8C3900",
"connectionReferences": {},
"connectorDisplayName": "Control",
"icon": "data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=",
"isTrigger": false,
"operationName": "Add_Days",
"operationDefinition": {
"type": "Scope",
"actions": {
"Date": {
"type": "Compose",
"inputs": "2022-04-04",
"runAfter": {},
"metadata": {
"operationMetadataId": "f03622ae-0e01-4a52-957b-476f810aea4d"
}
},
"DaysToAdd": {
"type": "Compose",
"inputs": 10,
"runAfter": {
"Date": ["Succeeded"]
},
"metadata": {
"operationMetadataId": "22b2d012-3447-4ac0-80a4-24251d06ff99"
}
},
"Dates": {
"type": "Select",
"inputs": {
"from": "@range(0,365)",
"select": {
"Date": "@formatDateTime(addDays(outputs('Date'),item()), 'yyyy-MM-dd')",
"Day": "@dayOfWeek(addDays(outputs('Date'),item()))"
}
},
"runAfter": {
"DaysToAdd": ["Succeeded"]
},
"description": "range(0,365)",
"metadata": {
"operationMetadataId": "362d7f26-03cb-4a8c-a70e-8e75019a1061"
}
},
"Filter_array": {
"type": "Query",
"inputs": {
"from": "@body('Dates')",
"where": "@and(not(equals(item()['day'], 0)), not(equals(item()['day'], 6)))"
},
"runAfter": {
"Dates": ["Succeeded"]
},
"metadata": {
"operationMetadataId": "bc17d8a4-b5b8-4a5b-bd3a-34fcd2d0f2ae"
}
},
"TargetDate": {
"type": "Compose",
"inputs": "@body('Filter_array')[outputs('DaysToAdd')]['date']",
"runAfter": {
"Filter_array": ["Succeeded"]
},
"description": "body('Filter_array')[outputs('DaysToAdd')]['date']",
"metadata": {
"operationMetadataId": "19be198e-647c-40bf-9109-86261450a9ef"
}
}
},
"runAfter": {},
"metadata": {
"operationMetadataId": "ff58b7e7-a16e-4a1c-9ffe-b377e6b228ae"
}
}
}
Add Filtering of Public Holidays
It is possible to enhance the flow above so that it will also exclude public holidays in addition to the weekends.
First, setup a SharePoint list that contains all of the dates you want to skip. I setup my SharePoint list with two columns. Title and HolidayDay. This is how my list looks:
Next we can modify our flow to also exclude these dates. We need to add an additional three actions:
First, add a Get Items (SharePoint) action right below the existing Filter Array action and point it at your site and your Holiday Calendar list.
Then add a Select action, rename it to HolidayDays. Modify the select action so that it is in text mode by clicking the small grid icon on the right side of the Map field. Populate the From field with the value from the Get Items action. In the Map field choose the HolidayDay.
Next add a filter action and rename it to ExcludeHolidays. The from will be the body output of our original filter array. On the left side of the filter we choose the output from the HolidayDays action and choose the option does not contain on the filter. On the right hand side of the filter use the expression item()[‘Date’] – It is much easier to comprehend in a image:
Finally, modify the expression in the TargetDate compose action to:
body('ExcludeHolidays')[outputs('DaysToAdd')]['date']
Now your TargetDate output will exclude both weekends and any public holidays that you have specified.
