Hi @powerappuserph ,
We have some workarounds here. As you already found, you will need to work with the 'Created' column. You can filter the items for created date, returning only those created 5 business days ago or 10 business days ago.
I believe that the easiest way to count the business days is to just consider 7 days (for 5 business days) and 14 days (for 10 business days). As 5 business days will always match to 7 weekdays (ignoring any holidays), we can just choose to take this approach without any additional calculations.
If for any reason you need to extract the exact quantity of business days, you can follow the steps from this example:
Note 1 - This strategy works only for counting more 5 business days. If you need to count less than that, you may need a little more complexity into the flow
Note 2 - I'm aware that you are starting with Power Automate, so do not hesitate in asking for more clarification in any of the steps taken.
First, we are setting the 'future_date' variable as September 5th, which is exactly 14 days from now (or 10 business days):
Then we initialize another variable ('delay_days')to store the difference in days between today and the 'future_date' variable:
We are using this expression: int(split(dateDifference(startOfDay(utcNow()), variables('future_date')), '.')[0]), where:
- startOfDay() is used to set the utcNow() to the midnight, so our dateDifference expression will ignore any difference among hours of the two dates
- dateDifference() is used to calculate the difference in days, hours, minutes and seconds between two dates. It requires two arguments (in our case, startOfDay(utcNow()) and variables('future_date')), and it returns a timespan, which is a string. Here you have a timespan example: what we want is the number before the dot, which represents the quantity of days:
- split() is transforming the timespan string into an array. We are using the dot ('.') as separator, so everything that is before the dot (the quantity of days) will be the first array element, while everything that is after it will be the second array element.
- [0] is a notation to access the first element of the array, which is the quantity of days between the two dates. If you want more details about how to access array elements, check this article that I wrote recently: http://digitalmill.net/2023/07/12/using-loops-and-accessing-array-elements-in-power-automate/
- Finally, the int() expression transforms the current result into an integer. When you extract the first part of the array with the '[0]', you will have '14' as string (text), and not as a number. So int() will do this transformation.
After finding the quantity of days between two dates (stored into 'delay_days' variable), divide it for 7 and store it into an integer variable. It will count the number of complete weeks that you passed into 'delay_days' (for 8 days, will return 1 week, for 13 days, will return 1 week, for 15 days, will return 2 weeks).We are storing this result into a variable called 'week_count':
Expression: div(variables('delay_days'), 7). The div() is used to perform division operations (you cannot just use a slash, like you would do in Excel, Power BI or any programming language).
Finally, in a last action you will multiply the 'week_count' variable by 2 (to capture the quantity of days for the weekends) and subtract it from the 'delay_days' variable. We are storing it in a variable called 'delay_business_days':
Expression: sub(variables('delay_days'), mul(variables('weeks_count'), 2)). The mul() expression is used for multiplication operations, and sub() for subtractions.
These are the outputs in the flow run:
Now you can use your 'delay_business_days' variable to display how many business days passed between the two dates.
Let me know if it works for you or if you need any additional help!
-------------------------------------------------------------------------
If this is the answer for your question, please mark the post as Solved.
If this answer helps you in any way, please give it a like.
http://digitalmill.net/
