cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
eobrie22
Helper I
Helper I

Collect all dates within a date range

Hi,

 

I have a bit of a challenging situation here.

 

First I am collecting all booking requests that are of WorkWeekType.value="Weekends Only". 

The collection contains a start and end date that could span over multiple weeks.

I then need to collect all the dates in that range, for each booking, and get all the weekend dates only.

I then need to create new bookings for each item on just the weekend dates - I need to create new start and end dates and new booking for each weekend in the original date span and patch it back to col_combinedrequests. The issue is that I need to do this for multiple booking items at the same time.

 

Any help would be appreciated.

 

Thanks!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
WiZey
Super User
Super User

Hello @eobrie22 ,

 

Let's see if I got this right...

 

You start with a collection of bookings. Each booking is a record containing a start date and an end date.

 

You want to get a collection of every date whose day is either saturday or sunday for each booking, starting from the start date and ending at the end date of said booking.

 

Let's go step by step.

 

First, we know we can determine if a week day is monday, tuesday, wednesday, etc. thanks to the "WeekDay()" function which works like this:

 

DayValue
Sunday1
Monday2
Tuesday3
Wednesday4
Thursday5
Friday6
Saturday7

 

So everytime we compute a date, we can check if it's a weekend if "WeekDay()" returns either "1" or "7".

 

Second, to compute a range of date, we need to know three things:

  1. The starting date of the range
  2. The ending date of the range
  3. The number of days between start and end

As you already know the start and end date, we only need to calculate the number of days between both with "DateDiff()".

This will help us define a "Sequence()", which will return a table with incremental numbers starting from 1 to the number we gave it.

 

So if we give it "5", "Sequence()" will return [ 1 ; 2 ; 3 ; 4 ; 5 ].

 

Then if we give it the number of days between start and end date, "Sequence()" will give us an incremental list of number equivalent to the range from start to end.

 

With this, we can mix-up "ForAll()", "Sequence()" and "DateAdd()" to quickly create a table of incremental date starting from start date up to end date.

 

ForAll(
  Sequence(
    DateDiff(StartDate, EndDate)
  ),
  DateAdd(StartDate, Value, Days)
)

 

All you need now is to apply a "Filter()" to remove any date which "WeekDay()" is neither "1" or "7".

 

This should give you a strong head up. I hope this was helpful to you.

We'll be happy to assist if you still meet some difficulties.

View solution in original post

5 REPLIES 5
WiZey
Super User
Super User

Hello @eobrie22 ,

 

Let's see if I got this right...

 

You start with a collection of bookings. Each booking is a record containing a start date and an end date.

 

You want to get a collection of every date whose day is either saturday or sunday for each booking, starting from the start date and ending at the end date of said booking.

 

Let's go step by step.

 

First, we know we can determine if a week day is monday, tuesday, wednesday, etc. thanks to the "WeekDay()" function which works like this:

 

DayValue
Sunday1
Monday2
Tuesday3
Wednesday4
Thursday5
Friday6
Saturday7

 

So everytime we compute a date, we can check if it's a weekend if "WeekDay()" returns either "1" or "7".

 

Second, to compute a range of date, we need to know three things:

  1. The starting date of the range
  2. The ending date of the range
  3. The number of days between start and end

As you already know the start and end date, we only need to calculate the number of days between both with "DateDiff()".

This will help us define a "Sequence()", which will return a table with incremental numbers starting from 1 to the number we gave it.

 

So if we give it "5", "Sequence()" will return [ 1 ; 2 ; 3 ; 4 ; 5 ].

 

Then if we give it the number of days between start and end date, "Sequence()" will give us an incremental list of number equivalent to the range from start to end.

 

With this, we can mix-up "ForAll()", "Sequence()" and "DateAdd()" to quickly create a table of incremental date starting from start date up to end date.

 

ForAll(
  Sequence(
    DateDiff(StartDate, EndDate)
  ),
  DateAdd(StartDate, Value, Days)
)

 

All you need now is to apply a "Filter()" to remove any date which "WeekDay()" is neither "1" or "7".

 

This should give you a strong head up. I hope this was helpful to you.

We'll be happy to assist if you still meet some difficulties.

eobrie22
Helper I
Helper I

@WiZey 

Thankyou!

 

How do I do that for each individual booking record? And then how do I create new bookings with just the weekend dates?

eobrie22
Helper I
Helper I

and what if the request start date and end date is the same day? How would I modify that formula

I figured it out. I just need help fixing your formula as its not including the start date itself in the date range collection

WiZey
Super User
Super User

Congrats on figuring this out! Now about your next inquiry.

 

By default, "Sequence()" first iteration is « 1 ». But you can change it by specifying from which number the function must start.

 

https://docs.microsoft.com/en-us/power-platform/power-fx/reference/function-sequence#syntax

 

Basically, you could tell "Sequence()" to start from « 0 » instead of « 1 ». But remember to re-add the offset in the range, otherwise your result will be one day short.

 

Re-using my previous formulae, it should look like this:

 

ForAll(
  Sequence(
    DateDiff(StartDate, EndDate) + 1,
    0
  ),
  DateAdd(StartDate, Value, Days)
)

 

I hope this was helpful to you.

 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (5,044)