cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Thomas1
Regular Visitor

How to efficiently calculate date values between two given dates? Do until block is slow.

Hello community.

 

Right now im struggling to efficiently calculate date values between two given dates. Let me explain:

In my flow I'm getting items from a SharePoint list, there can be thousands of items in this list.

Each item has a column for "start date" and a column for "end date".

 

My task is to write one line into an XLSX or CSV file for each date in between start and end date.

 

Example: Start date of the item is March 1st, end date of the item is March 4th.

My final XLSX or CSV file needs to look like that:

 

"03-01-2021, Title of the item, Some other column value of the item, yet another column value of the item"

"03-02-2021, Title of the item, Some other column value of the item, yet another column value of the item"

"03-03-2021, Title of the item, Some other column value of the item, yet another column value of the item"

"03-04-2021, Title of the item, Some other column value of the item, yet another column value of the item"

 

Right now my approach is that im subtracting the start date from the end date to find out how many days are between those. I save this value in a variable called "daysBetweenStartAndEnd". I then use a "Do until" block which increases a counter from 0 until it reaches the "daysBetweenStartAndEnd" variable value. Within the Do until block im using the "Add to time" block to add the current counter value to the start date, like so:

 

03-01-2021 + counter value 0 = 03-01-2021

03-01-2021 + counter value 1 = 03-02-2021

03-01-2021 + counter value 2 = 03-03-2021

03-01-2021 + counter value 3 = 03-04-2021

Done.

 

I'm saving each calculated date value into an array for later use.

 

dountil.jpg

 

This works and results in the desired outcome, but it's slow.

 

As I said, my list can have thousands of items and the start and end date of those tend to be separated by hundreds of days, meaning for each item I will run through the Do until hundreds of times. For one single item with about 200 days in between, the Do until block takes like 3 minutes already. I even removed the Add to time block and the Append to array block for testing purposes (making the Do until do basically nothing), and it still took 2 minutes for this item. So running through a blank Do until seems to be very time consuming.

 

Is there any way I can make this more efficient? Any other approach I can try?

 

Thank you very much.

 

0 REPLIES 0

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (2,065)