cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
djw1005
Level: Powered On

Dates between two dates

Is there a way of creating an array of dates between a start and end  date?

 

For example 

01/12/2018 Start

05/12/2018 Finish

 

This would record 01/12/2018...02/12/2018 etc 

 

 

17 REPLIES 17
Super User
Super User

Re: Dates between two dates

Hi

You can try with Variables, Do Until, adddays() array() and union(). Both adddays() array() and union() are functions defined here https://docs.microsoft.com/en-us/azure/logic-apps/logic-apps-workflow-definition-language

 

Let's assume you have stored the StartDate inside a variable called the same. Also for EndDate. I guess both dates shall be defined in datetime string which match ISO 8601 format ('o'), in order to apply adddays() function later on.

 

 

So, you initialize two array variables (myArray and auxArray) with the following expression:

array(variables('StartDate'))

 

You also initialize two string variables (indexDate and auxDate) with the following dynamic content:

variables('StartDate')

 

then you add the Do Until loop:

 

set indexDate to the following expression:

adddays(auxDate,1)

 

set auxDate to the following dynamic content:

variables('indexDate')

 

set myArray to the following expression

union(auxArray,array(variables('indexDate')))

 

set auxArray to the following dynamic content:

variables('myArray')

 

The Do Until shall be stopped once you reach the following condition:

equals(variables('EndDate'),variables('indexDate'))

 

In case it's needed, there is an intesting thread explaining how to match ISO 8601 format ('o') here:

https://powerusers.microsoft.com/t5/Building-Flows/formatDateTime-to-ISO-8061-format-for-Create-Even...

 

I haven't tested it myself, let's hope I haven't missed and single quote, parenthesis etc.

Hope this helps





If anybody solved your question, remember to mark its post as a solution!

Proud to be a Flownaut!





Community Support Team
Community Support Team

Re: Dates between two dates

Hi @djw1005,

 

I agree with @efialttes’s thought almost, I think you have two variables to store StartDate and EndDate on your side, in addition, both dates should be defined in datetime string which match ISO 8601 format (‘o’).

I have made a test on my side and please take a try with the following workaround:

  • Choose a proper trigger, here I use Flow Button trigger.
  • Add a Compose action, which represents StartDate. Input parameter set to following formula:
addDays(utcNow(),42,'MM/dd/yyyy')
  • Add a Compose 2 action (Called EndDate on my side), which represents EndDate. Input parameter set to following formula:
addDays(utcNow(),162,'MM/dd/yyyy')
  • Add a Variables-> “Initialize variable” action, Name set to IndexDate, type set to String, Value set to Ouput content of “Compose” action.
  • Add a Variables-> “Initialize variable” action, Name set to DateArray, Type set to Array, Value set to following formula:
array(outputs('Compose'))
  • Add a “Do Until” action, left input box set to variable IndexDate, right input box set to Output content of “EndDate” action, in middle drop down, choose is equal to.
  • Within “Do Until”, Add a “Compose 3” action, Input set to following formula:
addDays(variables('IndexDate'),1,'MM/dd/yyyy')

 

Add “Set variable” action, Name choose IndexDate, Value set to output of “Compose 3” action.

 

Add “Append to array variable” action, Name set to DateArray, Value set to variable IndexDate.

  • Add a “Compose 2” action, Input parameter set to variable DateArray.

Image reference:10.JPG

 

11.JPG

The flow works successfully as below:14.JPG

 

 

12.JPG

 

13.JPG

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
djw1005
Level: Powered On

Re: Dates between two dates

I have followed the instructions but I get the following error when it's run

 

error.PNG

 

 

Super User
Super User

Re: Dates between two dates

Hi @v-xida-msft

"Append to Array Variable" action block is much much more intuitive than using union() function as I suggested

Thanks for sharing!





If anybody solved your question, remember to mark its post as a solution!

Proud to be a Flownaut!





djw1005
Level: Powered On

Re: Dates between two dates

Thanks I have now got that working.... it's now righting to a sharepoint list as well

 

My question is now, how could I use this information to search within flow for existing holidays. 

 

I have created a for each loop, but when I search for say the index date with the dates booked off field it doesn't find anything oddly

items1.PNG

Super User
Super User

Re: Dates between two dates

Hi

I guess the screenshot you are sharing is from your Sharepoint List, right? And also you want to iterate through the array items, right?

If so, take into account your sharepoint column is probable string based, so you need to convert it into an array first. Once you have a real array, you can add an Apply to Each

Hpe this helps





If anybody solved your question, remember to mark its post as a solution!

Proud to be a Flownaut!





djw1005
Level: Powered On

Re: Dates between two dates

What that range is the array that is generated from the between dates on the request.

 

What I want to do is take the existing holidays and see if the dates  have been booked. 

 

So for example if someone books 12/01/2017 to 12/05/2017 but then tries to book 12/03/2017 it should reject this.

 

Just not sure how to achieve it.

Super User
Super User

Re: Dates between two dates

OK, so you have implemented an Apply to Each using variables('DateArray') as the input, right?

If so, this Apply to Each will iterate over all the date items inside the array

Then I guess you want to compare each date item with a target date in order to avoid duplicates, right?

 

IMO, there is also another aproach with less computing effort if the vacation periods are long, that is, since the array contains all dates between Start Date and End Date, you can compare your target date with first array item and last array item. If so, please take into account you will probably need to convert it into a date format valid for MS Flow.

 

Hope this helps

 





If anybody solved your question, remember to mark its post as a solution!

Proud to be a Flownaut!





djw1005
Level: Powered On

Re: Dates between two dates

Really new to all this and it's very head scratching.....

 

So I have an array of say 

12/01/2017 

12/02/2017

12/03/2017

..

..

12/10/2017

 

How do I search each date, against the preexisting holidays?

 

Super User
Super User

Re: Dates between two dates

Hi again

I guess the preexisting holidays are stored in the Sharepoint List, I mean, the screenshot you shared a few moments ago, right?

If so, how many items per employee do you store in the Sharepoint list? One per vacation period so an employee can have more than one item in the SP list, or one item per employee, so you append different vacation periods in the same array?

Also, your Flow is the only entry point to create items in this SP list?

I mean, almosr everything depends on the approach you followed so far

BR

 





If anybody solved your question, remember to mark its post as a solution!

Proud to be a Flownaut!





djw1005
Level: Powered On

Re: Dates between two dates

Each item in the sharepoint list is an individual request that a person submits

 

There could be many items per employee....

 

I am beginnging to wish I'd not started this....

Super User
Super User

Re: Dates between two dates

Hi again

 

So, you need to implement a Get Items action block and filter by employee. You will get then an array of N SP list items, all of them vacation requests from the employee.

You need to add an Apply to Each taking this array of SP items as input.

And you also have a new vacation request stored in an array Flow variable as you followed @v-xida-msft instructions.

 

If your Flow is the only entry point to add items in the SP list, my suggestion is to store dates in a Flow valid format, I mean datetime string which match ISO 8601 format ('o').

 

Also you need to convert the SP column value where you store the array into a real array (I mean, on SP this is stored as a string). One option is to use string manipulation functions to remove both [ and ] from the string, then use split() function.

 

Then you can take Start Date from SP item vacation period, also End Date from SP item vacation period. I guess you can use first() and last() for such purpose.

 

Finally if either "new vacation request Start Date" or "new vacation request End Date" are between "employee SP item vacation period" you are currently evaluating, the new request shall be rejected and the Flow terminated (for example, via TErminate action block). IF not, then Apply to Each will keep on evaluating against the next "employee SP item vacation period"

 

In order to compare dates, there are several threads in this community explaining how to do it. Also this excellent post explains how to do it by using ticks() function

https://flow.microsoft.com/en-us/blog/email-digest-date-manipulations/

 

Hope this helps

 

 

 





If anybody solved your question, remember to mark its post as a solution!

Proud to be a Flownaut!





dhawalseth
Level: Powered On

Re: Dates between two dates

I know this post is an year old. But found this amazing article that helped.

 

Do it in PowerApps

pit850
Level: Powered On

Re: Dates between two dates

Hi,

 

for the first. thanks for the post.

I've been looking for some time a way to calculate the period between dates. 

Unfortunately, the function datediff is not available in flow.
So i have try with your flow. 
everything so good. Unfortunately, I can not put my dates in the array. the following error message occurs.
 
 ActionFailed. An action failed. No dependent actions succeeded.
 

InvalidTemplate. Unable to process template language expressions in action 'Verfassen' inputs at line '1' and column '2595': 'The template language function 'addDays' expects its first parameter to be a string that contains the time. The provided value is of type 'Array'. Please see https://aka.ms/logicexpressions#adddays for usage details.'.

 

What am i doing wrong?

Variable "Startdate"   

addDays(trigstartdate 1.pnggerBody()?['EventDate'],0,'JJJJ-MM-TT')
 
 
 
Variable "Enddate"
Enddate 1.png
addDays(triggerBody()?['EndDate'],0,'JJJJ-MM-TT')
 
 
Indexdate.png
array(triggerBody()?['EventDate'])
 
DateArray.png
array(outputs('Enddate_1'))
 
 
 
 
 

 

 

 

 

Shlomotioneagle
Level: Powered On

Re: Dates between two dates

Hey guys,

 

I was able to rebuild a working solution with help of the reference link in a previous link and the instruction here. The flow will provide the number of total days between two dates, weekend days and working days (Mon-Fri) in this period. The pictures are step by step from creating a new item on a sp list until the output. 

1.PNG2.PNG3.PNG4.PNG5.PNG

 

 

 

 

 

 

 

 

tsl
Level: Powered On

Re: Dates between two dates

Thanks for sharing, that really helped me a lot. What I can't find out, though, is how to subtract local holidays from the calculated days. 

 

Feels like I've tried everything but nothing works.

 

Used your guide above and everything works. But now I have to find out if one of the local holidays, on the list below, is equal one of the dates counted and then subtract with one from the number of days.

 

Could you help me do that?

 

 

 

 Holidays.png

dhawallseth
Level: Power Up

Re: Dates between two dates

Dear tsl,

IMO you have to add a case where if the local holiday exists in the selected date range, reduce the number of holidays by 1.

 

Following are my observations:

  1. I don't see the leave balance being calculated through the flow. Once the user applies for leave(s), the leave balance should be updated.
  2. Add a logic in probably the "dates array", that if a holiday falls between the date ranges (Start date and end date) do not increment the holiday counter. Just skip.

 

Regards,

Dhawal Seth

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 Release Wave 2 Plan

Power Platform 2019 Release Wave 2 Plan

Features releasing from October 2019 through March 2020.

thirdimage

Flow Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Flow Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 390 members 5,163 guests
Please welcome our newest community members: