Create collection of two date ranges compared

Like (0) Share

Report

Posted on 28 Jul 2023 10:44:55 by SimpleUser

Hello,

i'm trying to create a collection which is made up of every day in the year in one column (so 365 rows) and then looking at a SharePoint list which has a start date and end date column. I want the collection to add a column with a value of '1' for the corresponding date ranges used. Its for a leave request app so the new collection should be made up of every day of the year and then a 1 against the days that the individual is on leave.

I've made a collection of the 365 days (colYearCal) and thought i could create a new collection with a lookup to the sharepoint list, but its getting beyond me and i'm sure there's a simple answer.

Can anyone help please?

Categories:

General Questions

All responses (1)

Answers (1)

Verified answer

v-bofeng-msft on 31 Jul 2023 at 02:16:01

Like (0)

Report
Re: Create collection of two date ranges compared

Hi @SimpleUser ,

I've made a test for your reference:

I created a colYearcal for test

ClearCollect(colYearCal,ForAll(Sequence(365,0,1),{Date:DateAdd(Date(2023,1,1),Value,TimeUnit.Days),Count:0}))

I've made a collection to imitate the SharePoint list:

ClearCollect(SPlist,{StartDate:Date(2023,1,1),EndDate:Date(2023,1,2)},{StartDate:Date(2023,1,4),EndDate:Date(2023,1,4)})

Update colYearCal

UpdateIf(colYearCal,!IsBlank(LookUp(SPlist,Date<=EndDate && Date>=StartDate)),{Count:1})

Best Regards,

Bof