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

Get exact dates between two days

Hello to all,

 

I'm still working on my holiday-flow, where you can request the Start-Date and the End-Date of your prefered holidays in Microsoft Forms. I already get the lenght between these two dates.

 

But I want now find the weekend days or public holidays and remove these days from the requested days. For example the worker requests free days from Wednesday 18.09.2019 to Tuesday 24.09.2019. My flow calculate in this case 7 days but there are only 5 days of work (2 days weekend).

 

Is there any possibility to get the exact dates between the Start-Date and the End-Date, so I can do an Excel-file with all weekenddates and public holidays and the flow compares the requested dates with the Excel-file.

Or maybe you have some better Idea 🙂

 

Thank you for your help

 

breater

2 ACCEPTED SOLUTIONS

Accepted Solutions
Dual Super User
Dual Super User

Re: Get exact dates between two days

Here's what I would do.

 

1) create a loop and walk through each day between the start and end dates

2) use the DayOfWeek(date) function to get the day of the week for each day in the loop.  If its not a Sat. or Sunday then go to the next check

3) Check the date against an array of holiday dates.  If it isn't true for #2 or #3 then increment a variable counter

4) when you exit the loop the variable counter will have the correct number of days.



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

View solution in original post

Highlighted
Dual Super User
Dual Super User

Re: Get exact dates between two days

If you use the ticks() function you can translate the date into an integer and save it as a numeric variable.  If you translate both start and end dates that way you'll be able to do the loop based on that. 1 tick = 100 nanoseconds. There are 864,000,000,000 ticks in a day. So you can increment the startdate in ticks by the number of days you want to cover in each loop.  When startdate in ticks is greater than or equal to end date in ticks you exit the loop.



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

View solution in original post

4 REPLIES 4
Dual Super User
Dual Super User

Re: Get exact dates between two days

Here's what I would do.

 

1) create a loop and walk through each day between the start and end dates

2) use the DayOfWeek(date) function to get the day of the week for each day in the loop.  If its not a Sat. or Sunday then go to the next check

3) Check the date against an array of holiday dates.  If it isn't true for #2 or #3 then increment a variable counter

4) when you exit the loop the variable counter will have the correct number of days.



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

View solution in original post

breater
Level: Powered On

Re: Get exact dates between two days

@Pstork1Thank you, for your reply.

 

I tried to realise your Idea, but i saved the start and end date in a String-Variable so i can't increment the Date up in the loop until the Startdate is equal to Enddate and its not possible to save the date in an Int or Float-Variable. Do you have any idea for the loop, how i can realise this with a String-Variable or Array?

 

Regards

 

Maximilian

Highlighted
Dual Super User
Dual Super User

Re: Get exact dates between two days

If you use the ticks() function you can translate the date into an integer and save it as a numeric variable.  If you translate both start and end dates that way you'll be able to do the loop based on that. 1 tick = 100 nanoseconds. There are 864,000,000,000 ticks in a day. So you can increment the startdate in ticks by the number of days you want to cover in each loop.  When startdate in ticks is greater than or equal to end date in ticks you exit the loop.



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

View solution in original post

breater
Level: Powered On

Re: Get exact dates between two days

Thank you for your help.

 

This step works well. But now i can't use the dayofWeek()-Function because there you need a String for input and I just have an Int (from ticks()). And moreover I can't increment a String for the loop. Or is it possible to convert a tick back to a date?

Helpful resources

Announcements
firstImage

Better Together Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

firstImage

Incoming: New and improved badges!

Look out for new contribution recognition badges coming SOON!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Community Summit North America

The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (8,541)