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!
John Mark Folker says
I am also having an issue with the “Item()[Date]” in the ExcludeHolidays action as well. It does come up for me as dynamic content and says invalid expression as well.
paul coultrup says
HI Just a thought and I could be wrong but in your example to exclude weekends you have chosen to add 10 days to a date, which excludes weekends. If your goal is to add 10 business days, would it be easier to just add 14 days….and exclude the flow that filters out weekends ? If its a round number to add like 5, 10, 15, 20 etc then you just add the correct number of weekend days.
Matt says
Awesome flow!
I’m having an issue towards the last part of adding custom holiday days.
In the “ExcludeHolidays” filter, the “does not contain” with expression | item()[‘Date’] | as suggested in the instructions does not seem to work.
@Paul – would you be able to confirm that please if you don’t mind?
Thank you so much!
Becky says
You took me to the finish line with this solution. Thank you so much!
Paulie says
Glad it helped! Well done.
David Mahaffey says
Luke K, John Mark Folker, Matt, and anyone else having trouble with the holiday filter’s item()[‘Date’] element: try editing the filter in advanced mode and using this: @not(contains(body(‘HolidayDays’), item()[‘Date’]))
Riyaz meman (Techwithriyaz) says
Very nice some struggle is there but it fix superb work paul
Sam Fisher says
Hi,
I’m still new to Power Automate and can’t understand the function, ‘body(‘Filter_array’)[outputs(‘DaysToAdd’)][‘date’]’. How does this work?
CCA_Help says
How do you incorporate this into a ‘When an item is created’ flow from a Microsoft list? I want this flow to link to a list where an individual inputs a start date and then it works out the due date and puts it into a due date column. I successfully incorporated this flow into two lists that I have but when I attempt to put the ‘TargetDate’ into ‘Update item’ the flow automatically reverts to an ‘apply to each’ and does not work. Can anybody help?
Mary Payne says
Thank you for this! Extremely helpful!!! 5 stars to you Sir!
Pauline Anna Dahne says
Just to let you know the New Designer gives a circular reference error – the Classic Editor works a treat!