Forum Discussion
Do Until loop creating too many dates
Hello. I have created a flow that identifies the difference of days between Start Date and End Date in a SharePoint List. The flow then checks for weekdays and adds a row into an Excel table for each individual day between the Start Date and End Date that is not a Saturday and Sunday. The main logic of the flow is working correctly where there is more than one day between the Start Date and End Date. The issue I am having is when the difference of days is 0, i.e., the Start and End Date is the same, the flow is creating multiple days and adding them to the Excel table.
In the below screenshot I have created the variable varIndex and added a Compose - Day Difference step using the following expression
As you can see in the next screenshot even though the Output of Compose - Day Difference is 0, my Compose varDates step is composing several days and adding them to my Excel table. In this example, the SharePoint List item has Start Date 16/02/2024 and End Date 16/02/2024. But instead of just adding one day into the Excel table, it's creating entries from 16/02/2024 to 16/04/2024. There is another list item where it composed the same number of dates where Start Date is 08/02/2024 and End Date is 08/02/2024 Result: 08/02/2024 to 08/04/2024.
Can anyone provide insight on why the flow is creating several days instead of one day? Hopefully I have provided enough information with the above but let me know if more info is needed. TIA.
MAVOTRP I don't know what else your flow is doing but you can have the following formula in a calculated column in your SharePoint list to calculate the difference in working days between a StartDate column and an EndDate column:
=(DATEDIF([StartDate],[EndDate],"D"))-INT(DATEDIF([StartDate],[EndDate],"D")/7)*2-IF(WEEKDAY([EndDate])<WEEKDAY([StartDate]),2,IF(OR(WEEKDAY([EndDate])=7,WEEKDAY([StartDate])=1),1,0))+1Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)- Hi Rob thanks for your response. The main objective of the flow is to take the individual working days between the Start and End dates in SharePoint then load them into an Excel table. The flow works fine when the number of days is 2 or more. For example:
Start Date 07/03/24 and End Date 08/03/24.
The flow correctly calculates 2 days and inputs 07/03/24 and 08/03/24 into the Excel table individually.
When it is for one day only, for example:
Start Date 07/03/24 and End Date 07/03/24.
For some reason even when my Compose - Day Difference step has an output of 0, the flow will create individual days from 07/03/24 to 07/05/24 and enter them into the Excel table. (see second screenshot in original post)
Every time there is a single day entry, it creates two months-worth of entries in Excel. But for 2 or more days, the logic works correctly and the outputs are correct.
