Get exact dates between two days

[Anonymous]

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

[Pstork1 · Accepted solution]

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.

[Anonymous]

@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

[Pstork1 · Accepted solution]

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.

[Anonymous]

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?