Power Automate – Add Business Days to Date (Excludes Weekends)

Hey folks,

today I came across a problem on within the Power Platform forum on how to “Add days to a date without weekends”. Thanks Matthew Devaney who posted a blog post about “How to add business days to a date in Power Apps(Excludes Weekends)”, so I could use this as an guideline to translate it into Power Automate. Here you will find the code on how to add business days to a date in Power Automate: The setup is pretty simple, I just added a trigger “Manually trigger a flow” with a date input “myDate” (triggerBody()[‘date’]) and a number input “addBusinessDays” (triggerBody()[‘number’]). Next I added a “compose” action and used the following code:

addDays(
    triggerBody()['date'],
    add(
        triggerBody()['number'],
        mul(
            div(
                sub(
                    add(
                        sub(
                            dayOfWeek(triggerBody()['date']),
                            1
                        ),
                        triggerBody()['number']
                    ),
                    1
                ),
                5
            ),
            2
        )
    )
)

So what’s happending here? Other than in Power Apps, there are two things missing. Using “dayOfWeek()” only allows us to add a timestamp and not a date and start of the week. Instead of defining the first day to Monday in our formula, we always start on a “Sunday” and are not able to change this. For calculation this means:

To still get the right calculation, we subtract 1 from our date in the beginning. The second formula missing is the round(), but we are kinda lucky and Power Automate automatically rounds down the value for us, so we don’t need to change anything here.

I just tested it with the same dates as Matthew did in his blogpost and I get the same results, but did not fully test it in every scenario (like leap year or within February) so please report if you notice any problems.

Thanks for reading, I hope it will help you!

Glück auf

Marvin