collect All Sunday dates between two selected dates

[ShaikSayyad]

Hi,

 I have three SharePoint columns named as 

    A. overView( type -Text) ,

    B. baseStartDate(type - Date)

    C.  baseEndDate(type - Date)

User will provide Text in overView either one of Weekly, Bi Weekly and Monthly, Hence I need Sundays date based on overView text like below

case_1

overView  baseStartDate  baseStartDate

Weekly     11/03/2021     30/04/2021

collection should be 

14/03/2021

21/03/2021

28/03/2021

25/04/2021

Case_2

overView  baseStartDate  baseStartDate

Bi Weekly     11/03/2021     30/04/2021

collection should be 

14/03/2021

28/03/2021

11/04/2021

25/04/2021

Case_3

overView  baseStartDate  baseStartDate

Monthly     11/03/2021     30/12/2021

collection should be 

04/04/2021

02/05/2021

06/06/2021

04/07/2021

01/08/2021

05/09/2021

03/10/2021

07/11/2021

05/12/2021

[RandyHayes]

@ShaikSayyad 

Sorry I didn't get back to you on your other post for this.  I ran into a question and didn't get a chance to send a reply.

My question was on the Monthly.  Your first date is based on the First Sunday of the Month, whereas the others (weekly and bi-weekly) seemed to be based on the next Sunday of the starting date.

Can you expand on the concept of the starting Sunday based on the three conditions?

[ShaikSayyad]

Hi Randy,

No worries 🙂

First thing I do not need three collections, I need only one collection based on below criteria

Assume some tasks were allocated to a person, Project Manager providing overView  ( weekly or Bi weekly or Monthly based on task weight ) in order to track those tasks, Since Dates are provided to task I need a collection where person can provide progress on allocated Sundays...

 coming to Monthly - If start date - 18/03/2021 , End date - 10/03/2022

I need all first Sunday dates from 4th Month/2021 to  3rd Month of 2022...

I hope this would clear you.

[RandyHayes]

@ShaikSayyad 

Yes...I would not provide you with something that would produce 3 collections...actually aiming for 0!

So getting all the dates except for the monthly was no problem.  The trouble comes currently with the Monthly because your rule is different.

So, I'm at the end of day here.  I will look over the formula in the morning.  

Please...if you don't hear back from me tomorrow, respond to this post again and I will get alerted.

[RandyHayes]

@ShaikSayyad 

Okay...so, since the month logic is different than the weekly and bi-weekly, the formula needed to account for that.

Here is the formula:

With({_startDate: DatePicker2.SelectedDate,
      _endDate: DatePicker2_1.SelectedDate,
      _span:Dropdown15.Selected.val},

    If(_span>=30,
        With({_mnth:Date(Year(_startDate), Month(_startDate)+1,1)},
            ForAll(Sequence(RoundUp(DateDiff(_mnth, _endDate, Months),0)+1),   
                {date: With({_mnthNext:DateAdd(_mnth, Value-1, Months)}, DateAdd(_mnthNext , 8-Weekday(_mnthNext), Days))}
            )
        ),

        With({_nextSunday: DateAdd(_startDate, 8-Weekday(_startDate), Days)},
            ForAll(Sequence(RoundUp(DateDiff(_nextSunday, _endDate, Days)/_span, 0)), {date: DateAdd(_nextSunday, (Value-1) * _span, Days)})
        )
    )
)

I tested with 2 datepickers and a dropdown with spans in it for Weekly, Bi-weekly and Monthly.

Here are your scenario results:

Weekly

Bi-weekly

Monthly

[ShaikSayyad]

Thank you very much, I was on other stuff....sorry for late response

please have a look into below screen 

class 2 is text field (could be Weekly, Bi Weekly and Monthly)

[RandyHayes]

@ShaikSayyad 
What is the purpose of a collection in this formula?

I cannot see your entire formula, but it looks to be the basic of a patch statement.

In this case, you don't need a collection.

Change your Formula To the following:

Collect('03 List_ Project Updates',
    ForAll(
        With({_startDate: ThisItem.'Base Start Date', //MAKING ASSUMPTION here that you are really referring to the current item in your gallery
             _endDate: ThisItem.'Base End Date',
             _span: ThisItem.'Class 2'}, // WHAT IS THIS IN YOUR LIST?
          
            If(_span>=30,
                With({_mnth:Date(Year(_startDate), Month(_startDate)+1,1)},
                    ForAll(Sequence(RoundUp(DateDiff(_mnth, _endDate, Months),0)+1),   
                        {date: With({_mnthNext:DateAdd(_mnth, Value-1, Months)}, DateAdd(_mnthNext , 8-Weekday(_mnthNext), Days))}
                    )
                ),

                With({_nextSunday: DateAdd(_startDate, 8-Weekday(_startDate), Days)},
                    ForAll(Sequence(RoundUp(DateDiff(_nextSunday, _endDate, Days)/_span, 0)), {date: DateAdd(_nextSunday, (Value-1) * _span, Days)})
                )
            )
        ),
        
        {Title: ThisItem.'Project ID',        
         'Update Date ': date
        }
    )
)

Please note the annotations in the above Formula.  I had assumed that Base Start Date and End Date were coming from the current record selection that you are doing this formula on (please correct if not).

Also, I don't know what Class 2 is in your list, but the context of this formula, it needs to be numeric.  Let me know what that is.

[ShaikSayyad]

Hi Randi,

If(
    CountRows(
        Filter(
            '03 List_ Project Updates',
            ThisItem.'Project ID' in Title
        )
    ) = 0, // to Eliminate duplicate records to the same projectID //
    Collect(
        collOneLinerDates,
        With({_startDate: ThisItem.'Base Start Date',
      _endDate: ThisItem.'Base End Date' ,
      _span:ThisItem.'Class 2'}, // Class 2 is a Text field which could have Weekly or Bi-Weekly or Monthly //

    If( _span >=30,
        With({_mnth:Date(Year(_startDate), Month(_startDate)+1,1)},
            ForAll(Sequence(RoundUp(DateDiff(_mnth, _endDate, Months),0)+1),   
                {OneLinerDate: With({_mnthNext:DateAdd(_mnth, Value-1, Months)}, DateAdd(_mnthNext , 8-Weekday(_mnthNext), Days))}
            )
        ),

        With({_nextSunday: DateAdd(_startDate, 8-Weekday(_startDate), Days)},
            ForAll(Sequence(RoundUp(DateDiff(_nextSunday, _endDate, Days)/_span, 0)), {OneLinerDate: DateAdd(_nextSunday, (Value-1) * _span, Days)})
        )
    )
)
));
ForAll(
    collOneLinerDates,
    Patch(
        '03 List_ Project Updates',
        Defaults('03 List_ Project Updates'),
        {
            Title: ThisItem.'Project ID',
            'Update Date ': OneLinerDate
        }
    )
)

Here, I need collection to create multi records and class 2 is a text field which holds Weekly or Bi-Weekly or Monthly

[RandyHayes · Accepted solution]

@ShaikSayyad 

Yes, none of that collection stuff is needed in this case - it's just extra steps and more formula to write and maintain. 

The formula below is all you will need to do this.

If(!LookUp('03 List_ Project Updates', ThisItem.'Project ID' in Title, true),

    Collect('03 List_ Project Updates',
        ForAll(
            With({_startDate: ThisItem.'Base Start Date',
                 _endDate: ThisItem.'Base End Date',
                 _span: Switch(ThisItem.'Class 2', "Weekly", 7, "Bi-Weekly", 14, "Monthly", 30)
                 }, 
          
                If(_span>=30,
                    With({_mnth:Date(Year(_startDate), Month(_startDate)+1,1)},
                        ForAll(Sequence(RoundUp(DateDiff(_mnth, _endDate, Months),0)+1),   
                            {date: With({_mnthNext:DateAdd(_mnth, Value-1, Months)}, DateAdd(_mnthNext , 8-Weekday(_mnthNext), Days))}
                        )
                    ),
   
                    With({_nextSunday: DateAdd(_startDate, 8-Weekday(_startDate), Days)},
                        ForAll(Sequence(RoundUp(DateDiff(_nextSunday, _endDate, Days)/_span, 0)), {date: DateAdd(_nextSunday, (Value-1) * _span, Days)})
                    )
                )
            ),
        
            {Title: ThisItem.'Project ID',        
             'Update Date ': date
            }
        )
    )
)