Patch a date range into a collection as single records

[TB05]

Hi,

I have a date range created using two Date variables called, varBonusEndDate and varBonusStartDate.

I'm wondering how do I put that range into a collection a single date entries?

I have a range of 5 days for example,  the varBonusStartDate = 2/7/2022 and varBonusEndDate = 2/11/2022.

I would like the collection to have one column called colDate and have 5 records in that collection to correspond to the range:

1. 2/7/2022

2. 2/8/2022, etc

 Appreciate your help!

Thanks

[RandyHayes]

@TB05 

Your formula would be:

Collect(yourCollection,
    ForAll(Sequence(DateDiff(varBonusStartDate, varBonusEndDate, Days), 0),
        {colDate: DateAdd(varBonusStartDate, Value, Days)}
    )
)

I hope this is helpful for you.

[TB05]

Thank you @RandyHayes for your quick response.  This works, except the last entry is not put into the collection.  The collection contains the first four dates.  I also created a gallery to confirm and there are only 4 dates in the collection.

[RandyHayes · Accepted solution]

@TB05 

That was dependent on if you needed start and end to be included.  If you want it included, then change the formula to:

Collect(yourCollection,
    ForAll(Sequence(DateDiff(varBonusStartDate, varBonusEndDate, Days) + 1, 0),
        {colDate: DateAdd(varBonusStartDate, Value, Days)}
    )
)

[TB05]

@RandyHayes AWESOME!!!  Thank you!!!  Have a great day.

[RandyHayes]

@TB05 

Thanks you too!  (actually now evening for me 😁 )

[selenimo]

Hi Randy, 

Where are we supposed to write this code? 

[SaschaGeng]

Hi @RandyHayes ,

unfortunately I could not find a proper solution yet in the community. I think that you solution is very close to it.

However I cannot convert and would appreciate if you could propose a solution:

I am searching a solution to convert an existing date range in a collection (let's say colTest1) with exiting columns like Title, StartDate, EndDate to a new collection (let's say colTest2) with single date column and Title as second column.

Example:

colTest1:

Title	StartDate	Enddate
AAA	15.02.2023	17.02.2023
BBB	22.02.2023	23.02.2023

colTest2

Title	Date
AAA	15.02.2023
AAA	16.02.2023
AAA	17.02.2023
BBB	22.02.2023
BBB	23.02.2023

Many thanks in advance

Sascha

[SaschaGeng]

Hi @RandyHayes 

I finally could solve by myself:

ForAll(
colTest1,
Collect(colTest2,
ForAll(Sequence(DateDiff(Startdate, Enddate, Days) + 1, 0),
{Date: DateAdd(Startdate, Value, Days),
Title: Title}
)
)
);

But many thanks for the key solution provided by you.

Sascha