Create multiple rows from one list item depending on date range

[ontheclock]

I have a Microsoft List where staff enter the dates they are on leave. 

Note: The dates in MS List are in American format

I'm trying to create a flow that checks the MS List above and creates multiple entries in Excel depending on the Leave Start Date and Leave End Date. The flow needs to add a row for each working day (Mon-Fri) between those dates excluding weekends as seen below;

I can create a flow that enters the working days into excel from MS List but stops adding entries when it gets to the weekend (pictured below)

Can this be achieved in Power Automate and if so how? 

[creativeopinion]

@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!

👉 Watch my tutorials on YouTube
👉 Tips and Tricks on TikTok

[ontheclock]

Thanks for the indepth support for this question creativeopinion. I have attempted to create this flow using a 'when an item is created or modified' trigger.

I cant get past the first Do Until and received the following error message

Appreciate your support

[creativeopinion]

@ontheclock It's hard to say without being able to see your expression. The error is indicating that the value you are passing isn't a timestamp. Please upload a screenshot of your expression.

[ontheclock]

Please see below;

[creativeopinion]

@ontheclock Looks like you are missing the closing parenthesis for the addDays() function. Right before the comma.

Hope this helps!

[ontheclock]

I thought so too but when I add the closing parenthesis and try to update PA says the expression is invalid

formatDateTime(addDays(triggerOutputs()?['body/LeaveStartDate']),variables('varIndex')),'dd/MM/yyyy')

Thanks

[creativeopinion]

@ontheclock Sorry, I missed this... you have an extra closing parenthesis here:

Your correct expression should be:

formatDateTime(addDays(triggerOutputs()?['body/LeaveStartDate'],variables('varIndex')),'dd/MM/yyyy')

[ontheclock]

Thank you! The flow is running without errors but the inputs in excel aren't correct. It also seems to have not included the 09/01/2024, 12/01/2024 and added 07/01/2024 which is a weekend? (the leave dates I inputted in MS List was 05/01/2024 - 12/01/2024)

I would like the 'Leave Date' column to generate only one date per row like below

Here is an overview of my flow

Here is my Apply to Each 

[creativeopinion]

@ontheclock You need to understand the logic of how you set up your flow to understand what the cause of your issue. To do this, you need to review the outputs of your flow run. As recommended in my original post you should be running a test at each point (where I recommended to run a test). It's a lot easier to troubleshoot your flow while you are building it than trying to build it all at once and troubleshoot it after.

First check that the initial array item has been set properly.

Then, check that your Integer variable has be set to 0.

Confirm the Day Difference in the Compose action to ensure that is what you are expecting. Remember 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. For this example, you are expecting 8 days between the two dates, however the number of whole days between is actually 7. This is fine since you'll be appending the last date to the array anyway.

Next, check that your increment variable is incrementing by one. The value should match the current apply to each number. Each time the Apply to each action loops through, it should add one to the value.

Next verify the dates, the dates in the Compose action should only be incrementing by ONE DAY. These dates should include the weekend days as the weekend days will be filtered out by the condition.

You need to ensure that the Compose action is outputting the correct number for the day of the week. 0 for Sunday, 1 Monday..... etc and 6 for Saturday. 

Next, verify the Condition action and ensure that if the date is a weekend day (eg. 0 for Sunday or 6 for Saturday) that the Condition outputs false. A date will only be appended to the variable if the Condition action returns true.

Before you even add the Apply to Each action to your flow, you should verify that the dates in the Compose actions are correct and that no weekend dates are included. If this is not the case, and weekend dates are included you need to troubleshoot your flow first before you continue to build out your flow. 

For more flow troubleshooting tips—check out This YT Tutorial: 5 Power Automate Troubleshooting FAQs and Helpful Tips for Creating Better Flows

The reason why you are getting the output in your Excel row is because of the dynamic content you inserted into the Leave Date. You inserted the output of the Compose action that has the array of dates (refer to screenshot above). This is why each row in your Excel is displaying all the dates and not the current date being looped through.

The way the Apply to Each action works is it's looping through each item in an array. For more tips and tricks on how to use the Apply to Each action refer to this YT Tutorial: 3 Mistakes YOU 🫵 are Making with the Apply to Each Action in your Microsoft Power Automate Flow 

What you need to use instead is the Current Item dynamic content. To better understand what the Current Item is (because the output will differ based on what you are looping through in the Apply to Each action). Add a Compose action and store the Current Item value and run a test.

Because you have a Do Until and an Apply to Each action loop in your flow—ensure you are selecting the correct Current Item dynamic content.

Run a test to verify the output. By running tests often it makes it easier to troubleshoot. Use Compose actions throughout your flow to help you troubleshoot but also to understand the logic of your flow.

[ontheclock]

I've reviewed the outputs of my flow and found the issue. The flow is identifying 09/01/2024 (Australian Date Format) as the 1st of September (01/09/2024 American Date Format) which is not a weekday. I've changed the regional settings in MS List to the correct timezone but still not working correctly. Would you work around this using a convert time zone action?

[Chriddle]

A slightly more elegant way to create this date array (that also handles holidays):

https://powerusers.microsoft.com/t5/General-Power-Automate/Working-day-difference-between-2-sharepoi...