GET IN TOUCH
+1-732-668-8002
+91-62843-00850
info@penthara.com
LOCATIONS
USA
131 Continental Drive
Suite 305
Newark, DE 19713
United States
India
SCO 515, Third Floor
Sector 70, Mohali
Punjab, 160055
Follow Us on Social -
02.08.2021

Using Power Automate to calculate Due Date for a task created in the issue tracker considering Business Days and no of workdays allocated for the task

SHARE THIS BLOG:
Table of contents
1. Introduction
• Objectives
• Prerequisites
2. Setting Up the SharePoint Site
• Creating the Work Progress Tracker List
• Adding a Column for Task Completion Workdays
• Making the Start Date Column Required
• Creating the Company Holiday List
3. Creating the Flow in Power Automate
• Initiating Variables for Calculations
• Calculating the Due Date
4. Conclusion

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.

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

     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.

     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.

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

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

Want to streamline your workflow with automated date calculations? Reach out to us for professional support!

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

     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.

     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.

                 i. Type: Integer

                 ii. Value: int(triggerOutputs()?['body/TaskDays'])

            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.

                 i. Type: Integer

                 ii. Value: 0

            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

                 i. Type: String

                 ii. Value: formatDateTime(triggerOutputs()?['body/StartDate'],'yyyy-MM-dd')

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

                 i. Type: String

                 ii. Value: @{variables('StartDate')}

Initialize Variable DueDate Value

     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.

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

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

     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.

     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.

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

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

     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

     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:

      a. ID

            i. Value: @{triggerOutputs()?['body/ID']}

      b.  Title

            i. Value: @{triggerOutputs()?['body/Title']}

      c. Start date

            i. Value: @{triggerOutputs()?['body/StartDate']}

      d. Task Completion WorkDays

            i. Value: @{triggerOutputs()?['body/TaskDays']}

      e. Due date

            i. Value: @{variables('DueDate')}

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

Skip (Yes/No)

Notes

1st April 2021

2nd April 2021

0

Yes

Company Holiday

 

3rd April 2021

0

Yes

Weekend

 

4th April 2021

0

Yes

Weekend

 

5th April 2021

1

No

Business Day

 

6th April 2021

2

No

Business Day

 

7th April 2021

3

No

Business Day

 

8th April 2021

4

No

Business Day

 

9th April 2021

5

No

Business Day

Need help setting up automated business day calculations in Power Automate? Contact us to get expert assistance!

Written By
Aneesh Kumar
Aneesh Kumar
M365 Solution Architect
peer reviewed By
JAsjit Chopra
chief executive officer
Graphics designed By
sanika sanaye
Creative Design Director

Leave a Reply

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

13 comments on “How to dynamically calculate working business days for a due date in Power Automate”

  1. Hi, my workflow is still not working using the steps above. I'm getting an error at 6a. Is the screenshot for 6a. Workdays correct? It looks just like step 6d. DueDate. Also, at which step do we input the working days/turnaround time (i.e. 2)?

    Reply

    1. Yes the screenshot is incorrect. We have updated it. Workdays should be used in step 6a with the below syntax:
      i. Type: Integer
      ii. Value: int(triggerOutputs()?['body/TaskDays'])

      Reply

  2. I already created flow following to your instruction, but in "Do until" loop took running so long more than 10 mins.

    Do you know what wrong with my flow? or I have to reduce the day which created in Company Holiday List?
    (Now I loaded Company Holidays included weekends for 2 years : 243 Records)

    Reply

    1. Yes, that might be an issue. Also, you do not need to capture weekends in the Holiday list since we have a condition to filter the weekends within the Do Until control loop.

      Reply

  3. Hi,

    I have a Dataverse Table with StartDate and EndDate and I am getting that table in a flow and then I want to filter out those rows whose StartDate and EndDate has weekends and add those rows in a different table.

    Can you please hel?p?

    Reply

    1. This needs a thorough setup of the flow to work with the dataverse which we will try to cover in a later blog. For now, we would suggest you to use the idea from above steps to capture data from dataverse to variables and then process it. Post that you may write the data to the table in dataverse.

      Note: Writing to Dataverse table needs a Premium operation.
      Ref: https://learn.microsoft.com/en-us/power-automate/dataverse/create

      Reply

  4. Hi,

    Thank you for sharing the flow.

    I have tried to create a flow taking this flow as a reference. My requirement is that I have a list that gets populated with content requests of different priorities. I want to calculate the due date based on the priority criteria and calendar days.

    There seems to a problem in step 6a. Initializing the variable value with int(triggerOutputs()?['body/TaskDays']). It shows error:
    Unable to process template language expressions in action 'Initialize_variable_WorkDays' inputs at line '0' and column '0': 'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.

    I made the value 0 and tested it.

    Another error in step 6c:
    Unable to process template language expressions in action 'Initialize_variable_StartDate' inputs at line '0' and column '0': 'The template language function 'formatDateTime' expects its first parameter to be of type string. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#formatdatetime for usage details.'.

    Removed the format and tested with blank value.

    Another error in step 7/8

    Unable to process template language expressions in action 'Compose_IncrementStartDate' inputs at line '0' and column '0': 'In function 'addDays', the value provided for date time string '' was not valid. The datetime string must match ISO 8601 format.'.

    Please check the flow once with these errors and help.

    Thanks,
    Ray

    Reply

  5. Thank you so much for writing this blog. I was struggling with the best way to process this logic.

    I have gone through all the steps but I found an issue.
    Step 6c - We create DueDate with no value
    Step 8 - Compose to increment StartDate, but the variable in the instruction is DueDate:
    addDays(Variables('DueDate'),1,'yyyy-MM-dd'). This results in an error as we cannot add days to a blank value.

    ERROR:
    InvalidTemplate. Unable to process template language expressions in action 'Compose_to_increment_StartDate' inputs at line '0' and column '0': 'In function 'addDays', the value provided for date time string '' was not valid. The datetime string must match ISO 8601 format.'.

    I surmised that we should initalize the DueDate with the same expression as the StartDate, and this now works.

    Thank you again.

    Reply

    1. Hi Fleur,

      There was an issue in the Due Date step - we have fixed it and updated the post. Please re-check the steps and implement it.

      Sorry for the inconvenience caused !

      Regards,
      Jasjit

      Reply

  6. Hi, can this workflow be used "When an item is created or modified"? I want to calculate the due date once the status is updated to "Submitted" not when the item is created. On step 6, what is "TaskDays" referring to as I don't see that column on any of the sharepoint lists? I'm also getting an error message in the Flow checker for the variable DueDate: "Actions in this flow may result in an infinite trigger loop. Please ensure you add appropriate conditional checks to prevent this flow from triggering itself."

    Reply

    1. You are most likely missing step 6(d) where we initiate DueDate variable.
      And yes, you can use this flow for "When an item is created or modified".
      Hope this helps!

      Reply

      1. Hi Reshma,

        We had to update our blog. There was a mistake in the Due Date step. Sorry for the inconvenience caused.

        Regards,
        Jasjit

        Reply

More From This Category

Asset & Inventory Management (AIM) App: Deployment Guide

This deployment guide is designed to help admins effectively deploy the Asset & Inventory Management (AIM) app to their environment. It provides step-by-step instructions and information about deploying the app in the environment.

Read More
Starfleet Stash App: Deployment Guide

This deployment guide is designed to help admins effectively deploy the Starfleet Stash app to their environment. It provides step-by-step instructions and information about deploying the app in the environment.

Read More
Top 5 Reasons to use Microsoft Power Platform for your Business.

In today's fast-paced and ever-changing business landscape, companies are constantly looking for ways to optimize their processes, reduce costs, and increase efficiency. The Microsoft Power Platform offers a range of solutions that can help businesses achieve these goals.

Read More
1 2 3 4
chevron-downchevron-right