Dates between two dates

[djw1005]

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 

[efialttes]

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

[v-xida-msft]

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:

The flow works successfully as below:

Best regards,

Kris

[djw1005]

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

[efialttes]

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!

[djw1005]

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

[efialttes]

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

[djw1005]

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.

[efialttes]

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

[djw1005]

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?

[efialttes]

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

[djw1005]

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....

[efialttes]

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

[dhawalseth]

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

Do it in PowerApps

[pit850]

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(triggerBody()?['EventDate'],0,'JJJJ-MM-TT')

Variable "Enddate"

addDays(triggerBody()?['EndDate'],0,'JJJJ-MM-TT')

array(triggerBody()?['EventDate'])

array(outputs('Enddate_1'))

[Shlomotioneagle]

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. 

[tsl]

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?

 

[dhawallseth]

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