How to Calculate a New Date X Business Days After a Specific Date in Dataverse

When working with dates in business processes, especially in automation scenarios like Dataverse and Power Automate, calculating the correct date after adding a specific number of business days becomes essential. However, this isn't as simple as just adding a set number of days to a date. You need to account for weekends and company holidays, which can complicate things.

In this article, I’ll walk you through how to accurately calculate a new date, X business days after a starting date, while excluding weekends and holidays. We will use Dataverse and Power Automate (Flow) to build this solution. Here’s how you can do it:

Step 1: Prepare Your Data

Before diving into the flow, let's first outline the data you'll need:

1. Date Fields: You need tables that store the two key date values: The start date, from which you'll calculate the new date, The business days, the number of days you need to add to the start date

2. Holidays Table: Create a table that stores your company's holiday dates. These must be stored as date values to properly filter against them later in the flow

Step 3: Create the "Do Until" Loop

Since we need to add business days while skipping weekends and holidays, we will use a “Do Until” loop. This loop will continue until the Counter equals the Workdays.

Inside the loop:

1. Increment the Due Date: The date is incremented by 1 day using the addDays() function: addDays(formatDateTime(variables('varDueDate')), 1)

2. Set the Due Date: The new date is stored in the Due Date variable.

Step 4: Check for Weekends

Now we need to check if the new date falls on a weekend. Using the dayOfWeek() function, we can check if the day is a Saturday (6) or Sunday (0). If it's a weekend, we skip that day and continue the loop.

Step 5: Check for Holidays

Now we need to make sure the date doesn’t fall on one of your company's holidays. Since holidays are stored in a Dataverse table, we need to filter for dates that fall within a given range.

Important: Dataverse stores dates with time values. So, to ensure your holiday check works, we need to compare the holiday date against the Due Date. We do this by checking if the holiday date is greater than or equal to the Due Date and less than or equal to the Due Date + 23 hours. Here's the formula:

formatDateTime(variables('varDueDate'), 'yyyy-MM-ddTHH:mm:ss+00:00')

This ensures we are comparing the Due Date in the correct format and taking time into account when filtering your holiday table..

Step 6: Increment the Counter or Retry

Once you’ve checked for weekends and holidays, you can take the following actions:

• If the date is valid (i.e., it’s a weekday and not a holiday), increment the Counter by 1.

• If the date is a weekend or holiday, do not increment the counter, and the loop will run again, moving the Due Date forward by 1 day until it’s a valid workday.

Step 7: Update the Original Record

Once the loop completes (i.e., when the Counter equals the Workdays), the Due Date is updated in the original record. This is the final date after adding the specified number of business days, excluding weekends and holidays.

Conclusion

This approach ensures that you can add a specified number of business days to any given date in Dataverse while properly accounting for weekends and holidays. It’s a straightforward way to handle date calculations in workflows, providing accurate due dates and helping streamline business processes.

By leveraging Power Automate (Flow) and Dataverse tables to manage your holiday data, you ensure that your organization’s unique schedule is respected, leading to smoother operations and more accurate automation.

Thank you to Penthara Techonologies who originally created the process, but utilizing SharePoint list data.

Feel free to tailor this solution to your specific needs. I hope this helps you streamline date calculations in your workflows! If you have any questions or improvements to share, feel free to reach out.