Collect all dates between start and end date

(1) Share

Report

Posted on by SSJDK

Hi Experts

I'm building an app where i will like to get a complete collection of days a person i working on a project.

I have a collection of people, start and end date and the project.

I will like to make a collection with:

Date

Name

Project

One item for every day.

I will then make a gallery with all people, and dates and the color in dates people are on projects just be doing a lookup on name and date in the new collection.

I can't make the loop that will make an item between dates to work, can someone help me on this matter?

People data

ClearCollect(
 PersonSchedule,
 { ProjectName: "Project A", Person: "John Doe", StartDate: DateValue("2024-07-01"), EndDate: DateValue("2024-03-10") },
 { ProjectName: "Project B", Person: "Jane Smith", StartDate: DateValue("2024-07-03"), EndDate: DateValue("2024-07-05") },
 { ProjectName: "Project C", Person: "Michael Brown", StartDate: DateValue("2024-07-05"), EndDate: DateValue("2024-07-18") },
 { ProjectName: "Project D", Person: "Sarah Johnson", StartDate: DateValue("2024-07-07"), EndDate: DateValue("2024-07-20") },
 { ProjectName: "Project E", Person: "David Lee", StartDate: DateValue("2024-07-09"), EndDate: DateValue("2024-07-22") },
 { ProjectName: "Project F", Person: "Emily Davis", StartDate: DateValue("2024-07-11"), EndDate: DateValue("2024-07-25") }
)

Result

Date	Name	Project
2024-07-01	John Doe	Project A
2024-07-02	John Doe	Project A
2024-07-03	John Doe	Project A
2024-07-03	Jane Smith	Project B
2024-07-04	Jane Smith	Project B
2024-07-05	Jane Smith	Project B

And so on.

This will make it quit simple to work with, maybe not the smartest way, but quit sure it will work.

SSJ

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Daniel Bocklandt 5,099 Super User 2025 Season 2 on at

Like (1)

Report
Hey @SSJDK ,

Can you try something like this?
ForAll(Sequence(DateDiff(StartDate,EndDate), Collect(PersonSchedule;{ProjectName:"Project A", Person:"John Doe"; Date: StartDate+ThisRecord.Value}))

Let me know if it helped you.

Was this reply helpful? Yes No
Verified answer

WarrenBelz 153,026 Most Valuable Professional on at

Like (3)

Report
HI @SSJDK ,

This is made much harder with the different date ranges

Clear(colSummary); ForAll( PersonSchedule As _Data, ForAll( Sequence( DateDiff( _Data.StartDate, _Data.EndDate, TimeUnit.Days ) + 1 ), Collect( colSummary, { ProjectName: _Data.ProjectName, Person: _Data.Person, Date: DateAdd( _Data.StartDate, Value - 1, TimeUnit.Days ) } ) ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
SSJDK 23 on at

Like (1)

Report

WOW That did exactly what i want it to do! I bit slow (not you), but i works! 😄

Huge tanks!

Was this reply helpful? Yes No