cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
0365
New Member

Microsoft Flow to calculate date differences between two dates and get regular and work days results

Is it possible to calculate date differences in Microsoft Flow? See example below for calculating the date differences and getting regular and/or work days. 

 

 

Regular Days: 

 

StartDate:09/10/2018 

EndDate: 09/16/2018

Duration: 7 days

 

Work Days: 

 

StartDate:09/10/2018 

EndDate: 09/16/2018

Duration: 5 days

 

 

 

3 REPLIES 3
v-bacao-msft
Community Support
Community Support

Hi @0365,

I'm afraid there is no direct action or Expression to calculate date differences between two dates and get regular and work days.

But you can try this method:

First list all the dates between the two dates, then store in the array

You could  first create a flow through such a link, get all the dates between the two dates, and store them in an array.

https://powerusers.microsoft.com/t5/Building-Flows/Dates-between-two-dates/td-p/73128

 

44.PNG

45.PNG

Then by traversing the array, use dayofweek() function to determine which value the date is equal to, 0 for Sunday, and 6 for Saturday, so you can get the number of days on the weekend and get workdays.

You could follow the following method to traverse the array and get the corresponding Regular Days, weekdays, workdays: 

Condition formula reference:

@or(equals(dayOfWeek(items('Apply_to_each')), 0),equals(dayOfWeek(items('Apply_to_each')), 6))

Compose action-Regular Days Expression:

length(variables('DateArray'))

Compose action-WorkDays Expression:

sub(outputs('Regular_Days'),variables('Weekdays'))

46.PNG

47.PNG

Note: Since Do Until can only be executed 60 times, this method is limited to a date of 60 days or less.

The flow works successfully as below, start date 09/10/2018 ,end date 09/25/2018:

48.PNG

Please take a try.

Also you could post this idea in the Flow Ideas forum, so it might be considered for future releases.

https://powerusers.microsoft.com/t5/Flow-Ideas/idb-p/FlowIdeas

Best Regards,

Barry

 

 

 

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
manuelstgomes
Super User
Super User

Hi @0365 

I've discovered the solution to this issue for another user here on the community, and I have written a blog article that explains the solution in detail. Check it out here:
https://manueltgomes.com/microsoft/flow/find-next-business-day/

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

michalparal
Frequent Visitor

Hi,

I would like to ask if this solution is still working because I am folliwing this instructions step by step, but I am unable to get it working. Every time I get the same mistake, when The flow tries to Append to array variable: 

BadRequest. The variable 'DateArray' of type 'Array' cannot be initialized or updated with value '09/11/2018' of type 'String'. The variable 'DateArray' only supports values of types 'Array'.


I would appreciate any advice.
Thanks!

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.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

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.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Users online (1,485)