@ontheclock First you'll need to create an array of dates between your Leave Start Date and Leave End date. I'm not sure what the trigger of your flow is. However, for the demo below—I'll be using a manual trigger and a Get Item action to get a specific item from the SharePoint list. You can do the same for your case and once your flow is ready to go you can adjust the trigger if needed.
Remember to rename your actions to keep your flow organized. Test often so you can troubleshoot early before you build out your entire flow.
For more flow troubleshooting tips—check out This YT Tutorial: 5 Power Automate Troubleshooting FAQs and Helpful Tips for Creating Better Flows
In this tutorial I cover:
✅ How to troubleshoot a false Condition action result
✅ How to get dynamic content when it isn’t selectable from the list of dynamic content
✅ How to troubleshoot an Apply to Each action that isn’t looping through
✅ How to troubleshoot a skipped Apply to Each action
✅ How to troubleshoot a Filter Query
✅ How to use a SharePoint yes/no column in a Filter Query
✅ How to use Compose actions to troubleshoot a Power Automate flow
✅ How to troubleshoot multiple emails being sent
✅ How to troubleshoot multiple Teams messages being sent
Get Item
Add a Get Item trigger and insert the ID of an item in your SP list for testing purposes.
Initialize Array Variable
Initialize an Array variable to store the dates. You'll need to set the array upon initializing the variable with the start date.
Insert an expression between the square brackets. Use the formatDateTime() function.
Select the dynamic content tab and insert the Leave Start Date dynamic content from the Get Item action.
Add a comma, single quotes and insert your date format.
Run a test. Verify that the variable contains the leave start date in the format you've specified.
Initialize an Integer Variable
Initialize an integer variable. Set the value to 0.
Get the Number of Days Between Two Dates
You'll need to get the number of days between two dates. Use an expression:
div(sub(ticks(LeaveEndDate), ticks(LeaveStartDate)), 864000000000)
Replace the text in blue in the expression with the appropriate dynamic content from the Get Item action.
Run a test. Verify the Compose action is outputting the correct number of days between your two dates. Keep in mind that this expression will return the number of whole days between the two dates but does not include the start date itself in the count which is why the start date was added to the array upon initialization.
Create Dates
Add a Do Until action. In the first value field, insert the Integer Variable. Leave the operator as is equal to. In the second value field, insert the outputs from the Compose action above.
The Do Until action will loop through until the integer variable (varIndex) equals the number of days in the Compose action above. In this case it will loop through 7 times.
Add an Increment variable action. Select the integer variable and set the value to 1. This action will increment the variable by 1 each time the Do Until action loops through.
Generate the Date to Add to Array
Add a Compose action. We'll use this action to store the Date you'll be adding to the array.
We'll need to add days to the Leave Start Date each time the Do Until action loops through. Insert an Expression and use the addDays() function.
The addDays() function takes two parameters. A date and the number of days you'd like to add/subtract from a date. Use a positive number to add days, and negative to subtract.
Select the Dynamic Content tab and insert the Leave Start Date dynamic content from the Get Item action.
Add a comma and insert the varIndex (integer variable). Each time the Do Until action loops through it'll add the number in the variable to the Leave Start Date.
Wrap this expression in a formatDateTime() function. Go to the start of the expression by pressing the UP arrow key and type in formatDateTime with an opening parenthesis.
Go to the end of the expression by pressing the DOWN arrow key and add a comma and single quotes. Between the single quotes type in your date format.
Run a test. Verify the Compose action output.
Get the Day of the Week
Add a Compose action.
This Compose action is optional—however, I prefer using a Compose action to store a value I'm using in a Condition action to help with troubleshooting.
Insert an expression and use the dayOfWeek() function. This function will return the day of week as an integer that represents the day of the week, where Sunday is 0, Monday is 1, and so on up to Saturday, which is 6.
Click on the Dynamic content tab and insert the output from the Compose action above. Note: This is why it's important to name your actions!
Run a test. Verify the Compose action output.
Condition Check
Add a Condition action. You'll use this action to check if the date is a weekend day before appending it to the array of dates.
Add two conditions to your flow. Ensure you are using the And operator. In the first value field insert the output from the Compose action above. Change the individual condition operator to is not equal to. In the second value field, enter 0 and 6.
This condition action will check to see if the date is a Sunday or Saturday. If the date does not fall on the weekend, it'll continue with the actions you place in the YES branch.
In the YES branch, insert an Append to Array Variable action. Select the variable and insert the output from the Date to Date Compose action.
Add a Compose action outside of the Do Until action to store the variable so you can review the output.
Run a test. Review the output of the Compose action.
Apply to Each
Add an Apply to Each action to loop through each date in the array variable. Add any additional actions into the Apply to Each loop.
Hope this helps!
If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response!
|
