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.
Terri-Ann says
Genius, i will try this in my flow. Thank you for sharing this.
Frederic says
Brillant, as always. Eager to test this. Thanks Paulie!
David Lee says
Hey Paul – excellent flow you have built here, so thank you. I’m using it to create a recurring task in Microsoft planner.
One small issue – when I enter the starting date (input) as 2022-04-01 and am looking to find Business Day 15 (input), the output for TargetDate is 2022-04-27, one day later than it should be.
I get the feeling this is like a teams date addition problem. Any suggestions? Thanks.
Pamela says
Dear Paul – Many thanks for this great example, it helped me a lot. Any idea how can I get the business day -1? For example, today (19.05.2022) I need the output to be yesterday’s date (18.05.202). Thank you in advance,
Paulie says
Hi Pamela,
Just took a look at this for you. It can be done quite easily. Modify the two expressions in the select action.
For the Date value:
formatDateTime(addDays(outputs('Date'),mul(item(), -1)), 'yyyy-MM-dd')
For the Day Value:
dayOfWeek(addDays(outputs('Date'),mul(item(),-1)))
The key here is to multiply the current item by -1. So this removes days instead of adding them by changing the number of days to add to a minus figure. Reach out to me via the get in touch form if you get stuck and I will help you out.
Pamela says
Many thanks, Paulie. It worked!
Simon says
GREAT POST 5/5
Liz Mao says
Thanks for sharing!!!!It helps a lot!!!
Alberto Avendaño says
Thanks a lot for this very useful post!!
Joe says
Great flow 🙂
A small addition I made as I think when you start from a weekend date the counter should start from the next working day
For example in the flow I copied starting on the 2023-01-01 with 10 days will calculate as 2023-01-17
I think this should be 2023-01-16 so in the first filter I changed the query to
@and(and(not(equals(item()[‘day’], 0)), not(equals(item()[‘day’], 6))),not(equals(item()[‘Date’], outputs(‘Date’))))
which removes the first day if its a working day
Then changed the last step to
body(‘ExcludeHolidays’)[sub(outputs(‘DaysToAdd’),1)][‘date’]
Bara says
Thanks for the post – very helpful.
I am trying to create a flow that gets the Daystoadd field from the Microsoft list and then update the target date in the list. Each item have different Daystoadd.
Any advice would be appreciated (especially from the compose onwards)
Nick J says
This is fantastic. Simple, easy to follow even for a beginner to Power Automate. Far clearer than any similar guide I’ve seen. Thanks for sharing!
Paulie says
Glad it was helpful Nick
Dmytro says
Thanks,
I have extended your flow and added the opportunity to subtract days. Packed the child flow to the solution so the community can reuse it.
https://github.com/LessCodeHub/PowerAppsSolutions/tree/main/Add%20days%20excluding%20weekends%20and%20holidays.
lolosan says
Hi Dmytro, your package not working.
Luke K says
I’m struggling with the ‘does not contain’ value when attempting to exclude holidays – The “Item()[Date]” expression is marked as not valid. I also tried item({date]). Mine also looked like a red expression box, not a purple output box like your does (where it says ‘Date’ – is this dynamic content from a different action I’m not seeing?) Could you maybe share the advanced mode full expression like you did for the first filter action?
ian hayse says
A+ for creativity! Thank you for dreaming this up and posting it!