How to dynamically calculate working business days for a due date in Power Automate   Recently updated !


Objective

 

The objective of this article is to understand how we can use Power Automate aka Flow to calculate the due date for a task excluding holidays and weekends.

 

Prerequisites

 

  1. An account with SharePoint Site Collection Admin and Power Automate Access
  2. SharePoint Team Site

 

Steps to create the lists

 

  1. Create a SharePoint list Titled Work Progress Tracker using the default template ‘Work Progress Tracker’ as it has most of the columns needed for our demo. We will use this list to track the work items assigned to the team members.
CreateList


Tip: While creating the list we used the title as WPT to shorten the url and changed the list title to Work Progress Tracker post creation
.

2. Create a column to track the workdays assigned for a task. This will be used to calculate the Due Date excluding the weekends and holidays. The column will be created with the following details:

Name: Task Completion Workdays

Type: Number

Require that this column contains information: Yes

Number of decimal places: 0

Add to default view: Yes

Power Automate

3. Change the Start Date column in the list as a required column. If the item is created without the start date, the flow will fail. Hence we make this a required column.

Power Automate

4. Create a new list with the title Company Holiday List with the Blank list This will be used to track the company holidays that need to be excluded from the workdays for due date calculation.

Power Automate

5. Create a new column Date to track the Holiday date in the Company Holiday List with the following details.

Power Automate

6. Rename the Title column in the Company Holidays List to Holiday Description

Power Automate

 

 

Steps to create the flow in Power Automate

 

  1. We will be creating a new Flow from Power automate to automatically calculate and update the Due Date of a task in Work Progress Tracker list excluding the weekends and company holidays.
  2. Browse to the Power Automate site using the account that has Power Automate License to create a new Flow
  3. Selected New Flow > Automated Cloud flow
Power Automate

4. Specify the desired name (we have named it ITHelpdesk Task DueDate Calculator) and under the triggers select When an item is created (SharePoint). This will trigger the flow as soon as an item is created our Work progress tracker

5. In the trigger specify the site URL and the list name as shown below. The list is the work progress tracker list we created earlier to track work tasks.

Power Automate

6. Initiate the following variables which will be used further in the flow for calculations.

(a) Workdays – This will be used to keep a count of how many workdays have passed in our process to derive at a Duedate excluding the Holidays and Weekends.

      1. Type: Integer
      2. Value: int(triggerOutputs()?[‘body/TaskDays’])
Power Automate

(b) Counter – This will be used to keep a count on the number of loops needed in calculation of the Due date in the Do While loop further.

    1. Type: Integer
    2. Value: 0
Power Automate

(c) StartDate – This variable is used to capture the value of the StartDate field in the list and will be further used in calculating the due date

    1. Type: String
    2. Value: formatDateTime(triggerOutputs()?[‘body/StartDate’],’yyyy-MM-dd’)
Power Automate

(d) DueDate – This variable will be used to store the value of the due date during the calculation in the Do While loop further.

    1. Type: String
    2. Value: Blank
Power Automate

7. Add a Do Until control to loop the actions within, until the counter variable is equal to the variable workdays.

The purpose of this action is to create a loop to calculate working days, by excluding the weekends and company holidays. This is achieved by using the dayOfWeek expression and matching against the dates in the Company Holidays List.

Expression values:

Counter: variables(‘Counter’)

Workdays: variables(‘Workdays’)

 

Also change the limits in the Do until loop and set the count to blank which means this will be set to unlimited loops.

Power Automate

8. Add a Compose action in the Do until loop to increment the due date by a day.

Expression: addDays(Variables(‘DueDate’),1,’yyyy-MM-dd’)

Power Automate

9. Add a Set variable action in the Do until loop to set the variable DueDate. This will be further used to filter weekends and holidays.

Expression: outputs(‘Compose_to_increment_StartDate’)

Power Automate

10. Add a condition to filter the weekends. Expression: dayOfWeek(Variables(‘DueDate’))

    1. dayofWeek value for Sunday is 0 and for Saturday is 6
    2. Our condition is to filter out weekends from DueDate and loop to the next date if it does not match the condition.
    3. If the DueDate matches our condition, it moves to the next calculation step.
Power Automate

11. If the DueDate matches our condition (it is not a Saturday or Sunday), it moves to the next calculation step. Here we add a Get Items action from SharePoint list Company Holidays List with a filter query Date field equals DueDate variable value.

Expression: Date eq ‘@{variables(‘DueDate’)}’

This will give us an output if the DueDate value is a Company Holiday.

Power Automate

12. The next step is to add a compose action to calculate the length of the output from the Get Items action. This will help us calculate of the current DueDate is a holiday if the output is not Zero.

Expression: length(body(‘Get_items_Company_Holidays_List’)?[‘value’])

Power Automate

13. In the next step we add the Condition action to filter the holidays. If the output of the previous step of getting items where Date field does not match the Due date variable, the output length would be 0. This means that the current DueDate value isn’t a holiday and ehnce will be counted as a working Day for the Task. We use this logic to filter out the holidays.

Expression: @outputs(‘Compose_to_get_matching_count_with_Holidays’)

Power Automate

14. If the condition output is true (number of items in the Company Holiday List where Date equals Due Date is Zero), we increment the Counter Variable by 1

Power Automate

15. The Do Until loop will complete until the variable counter matches the variable workdays.

16. In the final step we update the DueDate in the Work Progress Tracker list for the current item using the SharePoint – update item action.

Expressions:

  1. ID
    1. Value: @{triggerOutputs()?[‘body/ID’]}
  2. Title
    1. Value: @{triggerOutputs()?[‘body/Title’]}
  3. Start date
    1. Value: @{triggerOutputs()?[‘body/StartDate’]}
  4. Task Completion WorkDays
    1. Value: @{triggerOutputs()?[‘body/TaskDays’]}
  5. Due date
    1. Value: @{variables(‘DueDate’)}
Power Automate

 

 

Conclusion

 

As you can see in the example, we will calculate the Due date when start date is 1st April’21, with 5 days duration. Consider 2nd April’21 to be a holiday.

The day for 2nd April’21 being a Company Holiday has been skipped hence the counter stays as 0. Also the days 3rd April’21 and 4th April’21 are skipped as it’s a weekend. Hence the Counting the Business Days, the DueDate will be 9th April’21.




Start Date Running Due Date Counter Counter Notes
2 April 2021 2nd April 2021 0 Yes Company Holiday
3 April 2021 0 Yes Weekend
4 April 2021 0 Yes Weekend
5 April 2021 1 No Business Day
6 April 2021 2 No Business Day
7 April 2021 3 No Business Day
8 April 2021 4 No Business Day
9 April 2021 5 No Business Day


AneeshKumar

Written By
Aneesh Kumar
(Practice Area Lead – Microsoft 365)

Jasjit

Peer Reviewed By
Jasjit Chopra
(CEO)

Leave a comment

Your email address will not be published. Required fields are marked *