How to add only working days to a date using Power Automate

“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.

div(outputs('Compose'),5)

You’ll need also the mod(…) expression to check if there’re no days left after the division, that the remainder is 0.

mod(outputs('Compose'),5)

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.

addDays(utcNow(),mul(outputs('Compose_2'),7))

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.

mod(outputs('Compose'),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.

dayOfWeek(outputs('Compose_3'))

Take also the number of the second day, after you added the remaining days.

dayOfWeek(outputs('Compose_4'))

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')
    )
)

Summary

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.

---

Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

First Name

Email address

Zero spam, unsubscribe anytime.

Leave this field empty if you’re human: