“Can I let Power Automate to add only working days to a date, without the need to manually select the end date?”
When you add days to a date in Power Automate, it’ll simply add the defined number of days. It doesn’t care about weekends, all days are equal. But that’s not true in your work life. You care about weekends. If there’s a task that should take 10 days of work, you understand it as 2 weeks, not a week and a half. As such, you want the calculated date to be 2 weeks from today, not exactly 10 days. And that’s what this post is about – how do you ignore the weekends and add only working days to a date?
Note: in this simplified version it’ll ignore national holidays.
Update: Paul didn’t like my solution and described a much better one that can skip also holidays, you can find it here.
Add full weeks to a date
The first step is to calculate how many full weeks you’re trying to add. One work week is 5 working days, therefore, you should divide the number of days by 5. Use the div(…) expression to divide the number of days by 5.
You’ll need also the mod(…) expression to check if there’re no days left after the division, that the remainder is 0.
Take the result, and if it’s more than 0 weeks and there’s no remainder, remove the last week. The reason is that the last up to 5 days should be always processed differently. It doesn’t have to be a full week, it can be only the 5 days.
if( and(greater(div(outputs('Compose'),5),0),equals(mod(outputs('Compose'),5),0)), sub(div(outputs('Compose'),5),1), div(outputs('Compose'),5) )
Multiply the result by 7, and add it to the date. You just added all the full weeks, e.g. to today’s date.
That was the easy part, now you have to add the remaining days.
Add the remaining days to a date
Adding the days is a bit more complex as there’re many more options. It’s less than a week, and it can be any number between 1 and 5 days to add. It can end on a weekend, or it can go over a weekend…
Start by calculating how many days it should be. That means using again the remainder of the division by 5.
If the remainder is 0, add 5 days (the days you subtracted before), otherwise use the remainder.
if( equals(mod(outputs('Compose'),5),0), 5, mod(outputs('Compose'),5) )
Add the number of days to the date with all the full weeks already added.
addDays( outputs('Compose_3'), if( equals(mod(outputs('Compose'),5),0), 5, mod(outputs('Compose'),5) ) )
The last step is to check the day of the final date with the dayOfWeek(…) expression. Firstly, the date shouldn’t be on the weekend. If the date is on Saturday or Sunday, you must add 2 extra days to skip it.
if( or( equals(dayOfWeek(outputs('Compose_4')),6), equals(dayOfWeek(outputs('Compose_4')),0) ), addDays(outputs('Compose_4'),2), outputs('Compose_4') )
Secondly, you must also check if there’s one more weekend to skip. Take the day number of the date after adding all the necessary weeks.
Take also the number of the second day, after you added the remaining days.
Now compare them. If the number of the second day is less than the first day, it went over the weekend and you must add 2 days. Combining it with the previous expression:
if( or( equals(dayOfWeek(outputs('Compose_4')),6), equals(dayOfWeek(outputs('Compose_4')),0) ), addDays(outputs('Compose_4'),2), if( less(dayOfWeek(outputs('Compose_4')),dayOfWeek(outputs('Compose_3'))), addDays(outputs('Compose_4'),2), outputs('Compose_4') ) )
As you can see in the solution above, you can make Power Automate add only working days to a date. The process could be split into 3 parts. Firstly, add the full weeks to the date. Secondly, add the remaining days to the date. And thirdly, make sure that the date doesn’t end on weekend and that you skip the last potential weekend.
There’re two moving parts in the solution – the date and the number of days to add. In this example I used today’s date and a compose action with the number of days, but you can of course load this data from somewhere else, e.g. SharePoint list.
10 thoughts on “How to add only working days to a date using Power Automate”
thanks for this. This works great! How would you take into account national holidays? Is there a blogpost from yours on that topic? Maybe a topic for a future post 😉
I don’t know how to include national holidays at this moment, I might take a look into it in the near future. 🙂
take a look on this article from Paulie, his solution is much better and can handle also holidays: https://www.tachytelic.net/2022/04/power-automate-flow-add-working-days-date/
Hi Tom. Thanks – this is awesome! Could you tell me how I can base the a start date on a date column on my list rather than today’s date? I tried replacing the utcNow() in Compose 3 but it didn’t take it. Thanks again!
Hi. Please disregard my question above. It actually worked. Thanks.
how to calculate working exclude weekend start date and end date and -5 to 5 days add and subtract kindly tell me only count working days not weekend. For example today is tuesday and calculate next 5 working days Wednesday,Thursday,Friday and skip the saturday sunday and add momday and tuesday total 5 days count exclude weekends. Thank You.
Paulie has much better article on adding working days: https://www.tachytelic.net/2022/04/power-automate-flow-add-working-days-date/
Well Done – Fantastic work. Good solve and learnt new tricks
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.