Power Platform Community Forum Thread Details

Calculating the Due Date for a File in a MS List

Hello,

I'm attempting to calculate the due date for a file in a MS List, however I'm running into quite a few issue due to the considerations that need to be accounted for each file.

These considerations are as follows:

The calculation must only take into account workdays, thereby excluding weekends and holidays.
The calculation has to take into account the priority of the file as it determines the length of time allotted for the file's review:
- High - 10 workdays
- Medium - 20 workdays
- Low - 30 workdays
- No Longer Required - Due Date must be left Blank
- Start Date is Blank - Due Date must be left Blank
The calculation must also take into account the Regional Area the file is assigned to, as each Regional Area has different holidays.
- I have created a separate MS List to list the holidays for each Regional Area.
- Each Regional Area has a column in this list where the dates for each holiday is listed (ex. North, South, East & West)

I have attempted to customize the solution outlined by Penthara, however I've not been successful.

If anyone is able to help and/or guide me with building this flow, it would be very much appreciated.

Thank you in advance!

Categories:

Business Process Flows

You've got too much going on there for my little brain to figure out :-)

However, I can help you with an expression to skip the weekends. You can use this expression:

addDays(utcNow(), add(if(or(equals(dayOfWeek(addDays(utcNow(), 8)), 0), equals(dayOfWeek(addDays(utcNow(), 8)), 6)), if(equals(dayOfWeek(addDays(utcNow(), 1)), 0), 1, 2), 0), 8))

Replace utcNow() with the date you want to add days to so it does not fall on Saturday or Sunday. The above expression is currently adding 8 days. Change just the 8 to any other value for the number of days you want to add. You could use a variable to set this in the expression. Therefore, you could use a condition to set the variable (NoDaysToAdd) based on high, medium, and low.

It basically checks to see the day of the week adding the number of days requested will fall on. If it falls on Saturday, it adds 2 more days to the value. If it would fall on Sunday, it adds 1 more day. Otherwise, it adds 0 additional days.

Without the above expression, adding 8 days to today would fall on Saturday, October 11. With the expression, it correctly calculated it for Monday, October 13: