Calculating Business days – Power Automate

One common requirement in business workflows is the need to calculate the number of business days (excluding weekends) between two given dates. Whether you’re tracking project timelines, managing leave requests, or automating other business processes, accurately determining business days is essential.

In this guide, we will walk you through the steps to calculate business days between two dates using Power Automate. By leveraging Power Automate’s capabilities and some logical functions, you can efficiently compute the number of working days and optimize your workflows for better efficiency and accuracy.

Let’s dive into the process of calculating business days in Power Automate, step by step.

Use Case

I need to determine a due date for an item based on its creation date. The due date should be set precisely two business days after the item’s creation date. Additionally, the calculation should consider weekends and any specified holidays, ensuring that the due date falls on a valid working day.

Solution

1. I start by converting current time in my time zone.

2. Next, I utilize the dayOfWeek function to find the current day of the week.

dayOfWeek(variables('UTC'))

In Power Automate, you can use the “dayOfWeek” function to determine the day of the week for a given date. This function returns a numeric value, where Sunday is 0, Monday is 1, Tuesday is 2, and so on, up to Saturday, which is 6. I calculate the business days using a switch statement

There could be scenarios where additional holidays need to be accounted for. For such scenarios, I have built a child flow that queries a Holiday list in SharePoint and returns the number of items that fall under two dates (Today and Due Date calculated above. I add that number to my Due Date calculated above.

addDays(variables('Due date'),outputs('Compose_-_Holidays_to_add'))

Full flow