cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Collect weeks between two dates

Hi,

 

I create app. In my App I have two date pickers. The first is "Start date" the second is "End Date".

I select Start Date and then the End Date.

 

After this I need to collect collection, where will be each week as a row (including year of the week in another column). 

How to do it?

 

Thank you for your ideas.

George

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Collect weeks between two dates

Hi @JiriS :

Please try this code:

Switch(
Weekday(DatePicker2.SelectedDate),
2,
Collect(MYdate,
   {NO:thevar+1,Monday:If(DateAdd(DatePicker2.SelectedDate,7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar,Days)),
    Tuesday:If(DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)),
    Wedesday:If(DateAdd(DatePicker2.SelectedDate,2+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,2+7*thevar,Days)),
    Thursday:If(DateAdd(DatePicker2.SelectedDate,3+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,3+7*thevar,Days)),
    Friday:If(DateAdd(DatePicker2.SelectedDate,4+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,4+7*thevar,Days)),
    Saturday:If(DateAdd(DatePicker2.SelectedDate,5+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,5+7*thevar,Days)),
    Sunday:If(DateAdd(DatePicker2.SelectedDate,6+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,6+7*thevar,Days))}),
3,
Collect(MYdate,
   {NO:thevar+1,Monday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)),
    Tuesday:If(DateAdd(DatePicker2.SelectedDate,7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar,Days)),
    Wedesday:If(DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)),
    Thursday:If(DateAdd(DatePicker2.SelectedDate,2+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,2+7*thevar,Days)),
    Friday:If(DateAdd(DatePicker2.SelectedDate,3+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,3+7*thevar,Days)),
    Saturday:If(DateAdd(DatePicker2.SelectedDate,4+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,4+7*thevar,Days)),
    Sunday:If(DateAdd(DatePicker2.SelectedDate,5+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,5+7*thevar,Days))}),
4,
Collect(MYdate,
   {NO:thevar+1,Monday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)),
    Tuesday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)),
    Wedesday:If(DateAdd(DatePicker2.SelectedDate,7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar,Days)),
    Thursday:If(DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)),
    Friday:If(DateAdd(DatePicker2.SelectedDate,2+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,2+7*thevar,Days)),
    Saturday:If(DateAdd(DatePicker2.SelectedDate,3+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,3+7*thevar,Days)),
    Sunday:If(DateAdd(DatePicker2.SelectedDate,4+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,4+7*thevar,Days))}),
5,
Collect(MYdate,
   {NO:thevar+1,Monday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)),
    Tuesday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)),
    Wedesday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)),
    Thursday:If(DateAdd(DatePicker2.SelectedDate,7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar,Days)),
    Friday:If(DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)),
    Saturday:If(DateAdd(DatePicker2.SelectedDate,2+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,2+7*thevar,Days)),
    Sunday:If(DateAdd(DatePicker2.SelectedDate,3+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,3+7*thevar,Days))}),
6,
Collect(MYdate,
   {NO:thevar+1,Monday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-4,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-4,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-4,Days)),
    Tuesday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)),
    Wedesday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)),
    Thursday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)),
    Friday:If(DateAdd(DatePicker2.SelectedDate,7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar,Days)),
    Saturday:If(DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)),
    Sunday:If(DateAdd(DatePicker2.SelectedDate,2+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,2+7*thevar,Days))}),
7,
Collect(MYdate,
   {NO:thevar+1,Monday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-5,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-5,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-5,Days)),
    Tuesday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-4,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-4,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-4,Days)),
    Wedesday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)),
    Thursday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)),
    Friday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)),
    Saturday:If(DateAdd(DatePicker2.SelectedDate,7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar,Days)),
    Sunday:If(DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days))}),
1,
Collect(MYdate,
   {NO:thevar+1,Monday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-6,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-6,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-6,Days)),
    Tuesday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-5,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-5,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-5,Days)),
    Wedesday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-4,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-4,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-4,Days)),
    Thursday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)),
    Friday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)),
    Saturday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)),
    Sunday:If(DateAdd(DatePicker2.SelectedDate,7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar,Days))}));Set(thevar,thevar+1)

 

 

Best Regards,

Bof

View solution in original post

9 REPLIES 9
Highlighted
Dual Super User II
Dual Super User II

Re: Collect weeks between two dates

Hi @JiriS 

 

I just posted something similar in the webinars gallery

https://powerusers.microsoft.com/t5/Webinars-and-Video-Gallery/Power-Apps-Date-Calculations-for-Busi...

 

--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

Thanks,
Reza Dorrani, MVP
YouTube
Twitter

Highlighted
Community Support
Community Support

Re: Collect weeks between two dates

Hi @JiriS :

Do you want to put the date of the week in the same record?

Do you want to collect weekly data based on start date and end date?

My suggestion is to save the records one by one in a circular way through the timer control.

I'v made a test for your reference:

1\Add two datepicker controls(DatePicker2/DatePicker2_1)

2\Add a timer control

OnSelect

 

Set(thevar,0) /*thevar is my custom variable*/

 

Duration

 

500 /*loop in 0.5s*/

 

Repeat:

 

If(thevar>RoundDown(DateDiff(DatePicker2.SelectedDate,DatePicker2_1.SelectedDate)/7,0),false,true) /*Determine the number of cycles based on the start and end dates*/

 

OnTimerEnd

 

Switch(
Weekday(DatePicker2.SelectedDate),
1,
Collect(MYdate, /*MYdate is my custom collection*/
   {Sunday:If(DateAdd(DatePicker2.SelectedDate,7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar,Days)),
    Monday:If(DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)),
    Tuesday:If(DateAdd(DatePicker2.SelectedDate,2+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,2+7*thevar,Days)),
    Wedesday:If(DateAdd(DatePicker2.SelectedDate,3+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,3+7*thevar,Days)),
    Thursday:If(DateAdd(DatePicker2.SelectedDate,4+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,4+7*thevar,Days)),
    Friday:If(DateAdd(DatePicker2.SelectedDate,5+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,5+7*thevar,Days)),
    Saturday:If(DateAdd(DatePicker2.SelectedDate,6+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,6+7*thevar,Days))}),
2,
Collect(MYdate,
   {Sunday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)),
    Monday:If(DateAdd(DatePicker2.SelectedDate,7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar,Days)),
    Tuesday:If(DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)),
    Wedesday:If(DateAdd(DatePicker2.SelectedDate,2+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,2+7*thevar,Days)),
    Thursday:If(DateAdd(DatePicker2.SelectedDate,3+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,3+7*thevar,Days)),
    Friday:If(DateAdd(DatePicker2.SelectedDate,4+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,4+7*thevar,Days)),
    Saturday:If(DateAdd(DatePicker2.SelectedDate,5+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,5+7*thevar,Days))}),
3,
Collect(MYdate,
   {Sunday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)),
    Monday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)),
    Tuesday:If(DateAdd(DatePicker2.SelectedDate,7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar,Days)),
    Wedesday:If(DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)),
    Thursday:If(DateAdd(DatePicker2.SelectedDate,2+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,2+7*thevar,Days)),
    Friday:If(DateAdd(DatePicker2.SelectedDate,3+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,3+7*thevar,Days)),
    Saturday:If(DateAdd(DatePicker2.SelectedDate,4+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,4+7*thevar,Days))}),
4,
Collect(MYdate,
   {Sunday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)),
    Monday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)),
    Tuesday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)),
    Wedesday:If(DateAdd(DatePicker2.SelectedDate,7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar,Days)),
    Thursday:If(DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)),
    Friday:If(DateAdd(DatePicker2.SelectedDate,2+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,2+7*thevar,Days)),
    Saturday:If(DateAdd(DatePicker2.SelectedDate,3+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,3+7*thevar,Days))}),
5,
Collect(MYdate,
   {Sunday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-4,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-4,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-4,Days)),
    Monday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)),
    Tuesday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)),
    Wedesday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)),
    Thursday:If(DateAdd(DatePicker2.SelectedDate,7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar,Days)),
    Friday:If(DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)),
    Saturday:If(DateAdd(DatePicker2.SelectedDate,2+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,2+7*thevar,Days))}),
6,
Collect(MYdate,
   {Sunday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-5,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-5,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-5,Days)),
    Monday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-4,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-4,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-4,Days)),
    Tuesday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)),
    Wedesday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)),
    Thursday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)),
    Friday:If(DateAdd(DatePicker2.SelectedDate,7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar,Days)),
    Saturday:If(DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days))}),
7,
Collect(MYdate,
   {Sunday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-6,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-6,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-6,Days)),
    Monday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-5,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-5,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-5,Days)),
    Tuesday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-4,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-4,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-4,Days)),
    Wedesday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)),
    Thursday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)),
    Friday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)),
    Saturday:If(DateAdd(DatePicker2.SelectedDate,7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar,Days))}));
Set(thevar,thevar+1)

 

70.gif

Best Regards,

Bof

 

Highlighted
Helper II
Helper II

Re: Collect weeks between two dates

That is really nice. It looks fantastic. I little change it because my startday of week is Monday.

 

But I have one question. How can I get from this the number of week (1-53) in year.

Or at least how can I get the number of week between date range (actual number of row). (see the print screen)

 

Thank you,

GeorgeBez názvu.png

Highlighted
Helper II
Helper II

Re: Collect weeks between two dates

Hi,

 

thank you for your idea, but how can I get the week number from this?

 

Thanks for explaining

Highlighted
Community Support
Community Support

Re: Collect weeks between two dates

Hi @JiriS :

Please try this code:

 

 

 

Switch(
Weekday(DatePicker2.SelectedDate),
1,
Collect(MYdate,
   {NO:thevar+1,Sunday:If(DateAdd(DatePicker2.SelectedDate,7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar,Days)),
    Monday:If(DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)),
    Tuesday:If(DateAdd(DatePicker2.SelectedDate,2+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,2+7*thevar,Days)),
    Wedesday:If(DateAdd(DatePicker2.SelectedDate,3+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,3+7*thevar,Days)),
    Thursday:If(DateAdd(DatePicker2.SelectedDate,4+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,4+7*thevar,Days)),
    Friday:If(DateAdd(DatePicker2.SelectedDate,5+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,5+7*thevar,Days)),
    Saturday:If(DateAdd(DatePicker2.SelectedDate,6+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,6+7*thevar,Days))}),
2,
Collect(MYdate,
   {NO:thevar+1,Sunday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)),
    Monday:If(DateAdd(DatePicker2.SelectedDate,7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar,Days)),
    Tuesday:If(DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)),
    Wedesday:If(DateAdd(DatePicker2.SelectedDate,2+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,2+7*thevar,Days)),
    Thursday:If(DateAdd(DatePicker2.SelectedDate,3+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,3+7*thevar,Days)),
    Friday:If(DateAdd(DatePicker2.SelectedDate,4+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,4+7*thevar,Days)),
    Saturday:If(DateAdd(DatePicker2.SelectedDate,5+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,5+7*thevar,Days))}),
3,
Collect(MYdate,
   {NO:thevar+1,Sunday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)),
    Monday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)),
    Tuesday:If(DateAdd(DatePicker2.SelectedDate,7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar,Days)),
    Wedesday:If(DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)),
    Thursday:If(DateAdd(DatePicker2.SelectedDate,2+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,2+7*thevar,Days)),
    Friday:If(DateAdd(DatePicker2.SelectedDate,3+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,3+7*thevar,Days)),
    Saturday:If(DateAdd(DatePicker2.SelectedDate,4+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,4+7*thevar,Days))}),
4,
Collect(MYdate,
   {NO:thevar+1,Sunday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)),
    Monday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)),
    Tuesday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)),
    Wedesday:If(DateAdd(DatePicker2.SelectedDate,7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar,Days)),
    Thursday:If(DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)),
    Friday:If(DateAdd(DatePicker2.SelectedDate,2+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,2+7*thevar,Days)),
    Saturday:If(DateAdd(DatePicker2.SelectedDate,3+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,3+7*thevar,Days))}),
5,
Collect(MYdate,
   {NO:thevar+1,Sunday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-4,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-4,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-4,Days)),
    Monday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)),
    Tuesday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)),
    Wedesday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)),
    Thursday:If(DateAdd(DatePicker2.SelectedDate,7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar,Days)),
    Friday:If(DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)),
    Saturday:If(DateAdd(DatePicker2.SelectedDate,2+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,2+7*thevar,Days))}),
6,
Collect(MYdate,
   {NO:thevar+1,Sunday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-5,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-5,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-5,Days)),
    Monday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-4,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-4,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-4,Days)),
    Tuesday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)),
    Wedesday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)),
    Thursday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)),
    Friday:If(DateAdd(DatePicker2.SelectedDate,7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar,Days)),
    Saturday:If(DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days))}),
7,
Collect(MYdate,
   {NO:thevar+1,Sunday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-6,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-6,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-6,Days)),
    Monday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-5,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-5,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-5,Days)),
    Tuesday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-4,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-4,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-4,Days)),
    Wedesday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)),
    Thursday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)),
    Friday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)),
    Saturday:If(DateAdd(DatePicker2.SelectedDate,7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar,Days))}));Set(thevar,thevar+1)

 

 

 

 

Best Regards,

Bof

Highlighted
Helper II
Helper II

Re: Collect weeks between two dates

Thank you, it works. But I have one question. How can I change the start of week to monday?

 

Thank you

Highlighted
Community Support
Community Support

Re: Collect weeks between two dates

Hi @JiriS :

Please try this code:

Switch(
Weekday(DatePicker2.SelectedDate),
2,
Collect(MYdate,
   {NO:thevar+1,Monday:If(DateAdd(DatePicker2.SelectedDate,7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar,Days)),
    Tuesday:If(DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)),
    Wedesday:If(DateAdd(DatePicker2.SelectedDate,2+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,2+7*thevar,Days)),
    Thursday:If(DateAdd(DatePicker2.SelectedDate,3+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,3+7*thevar,Days)),
    Friday:If(DateAdd(DatePicker2.SelectedDate,4+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,4+7*thevar,Days)),
    Saturday:If(DateAdd(DatePicker2.SelectedDate,5+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,5+7*thevar,Days)),
    Sunday:If(DateAdd(DatePicker2.SelectedDate,6+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,6+7*thevar,Days))}),
3,
Collect(MYdate,
   {NO:thevar+1,Monday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)),
    Tuesday:If(DateAdd(DatePicker2.SelectedDate,7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar,Days)),
    Wedesday:If(DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)),
    Thursday:If(DateAdd(DatePicker2.SelectedDate,2+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,2+7*thevar,Days)),
    Friday:If(DateAdd(DatePicker2.SelectedDate,3+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,3+7*thevar,Days)),
    Saturday:If(DateAdd(DatePicker2.SelectedDate,4+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,4+7*thevar,Days)),
    Sunday:If(DateAdd(DatePicker2.SelectedDate,5+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,5+7*thevar,Days))}),
4,
Collect(MYdate,
   {NO:thevar+1,Monday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)),
    Tuesday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)),
    Wedesday:If(DateAdd(DatePicker2.SelectedDate,7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar,Days)),
    Thursday:If(DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)),
    Friday:If(DateAdd(DatePicker2.SelectedDate,2+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,2+7*thevar,Days)),
    Saturday:If(DateAdd(DatePicker2.SelectedDate,3+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,3+7*thevar,Days)),
    Sunday:If(DateAdd(DatePicker2.SelectedDate,4+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,4+7*thevar,Days))}),
5,
Collect(MYdate,
   {NO:thevar+1,Monday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)),
    Tuesday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)),
    Wedesday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)),
    Thursday:If(DateAdd(DatePicker2.SelectedDate,7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar,Days)),
    Friday:If(DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)),
    Saturday:If(DateAdd(DatePicker2.SelectedDate,2+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,2+7*thevar,Days)),
    Sunday:If(DateAdd(DatePicker2.SelectedDate,3+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,3+7*thevar,Days))}),
6,
Collect(MYdate,
   {NO:thevar+1,Monday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-4,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-4,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-4,Days)),
    Tuesday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)),
    Wedesday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)),
    Thursday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)),
    Friday:If(DateAdd(DatePicker2.SelectedDate,7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar,Days)),
    Saturday:If(DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)),
    Sunday:If(DateAdd(DatePicker2.SelectedDate,2+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,2+7*thevar,Days))}),
7,
Collect(MYdate,
   {NO:thevar+1,Monday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-5,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-5,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-5,Days)),
    Tuesday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-4,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-4,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-4,Days)),
    Wedesday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)),
    Thursday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)),
    Friday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)),
    Saturday:If(DateAdd(DatePicker2.SelectedDate,7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar,Days)),
    Sunday:If(DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,1+7*thevar,Days))}),
1,
Collect(MYdate,
   {NO:thevar+1,Monday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-6,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-6,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-6,Days)),
    Tuesday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-5,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-5,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-5,Days)),
    Wedesday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-4,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-4,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-4,Days)),
    Thursday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-3,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)),
    Friday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-2,Days)),
    Saturday:If(DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)<=DatePicker2_1.SelectedDate && DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)>=DatePicker2.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar-1,Days)),
    Sunday:If(DateAdd(DatePicker2.SelectedDate,7*thevar,Days)<=DatePicker2_1.SelectedDate,DateAdd(DatePicker2.SelectedDate,7*thevar,Days))}));Set(thevar,thevar+1)

 

 

Best Regards,

Bof

View solution in original post

Highlighted
Community Support
Community Support

Re: Collect weeks between two dates

Hi @JiriS :

Is there anything else I can help you with?
If your porblem is resolved, please mark the solution as a solution so that more people can see it.

Best Regards,

Bof

Highlighted
Helper II
Helper II

Re: Collect weeks between two dates

Thank you for help!!!

 

George

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (6,713)