cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Check if DateTime value falls in a Date range Collection in Canvas App

Hello Experts,

I have a requirement where I have a Custom calendar in PowerApps where I can choose a date range with  Dropdowns to select time.

I have  a collection called 'DateRange' which holds all the start and end dates along with the time for the selected Date Range from the Calendar.

I need to filter my SharePoint List where I have 2 dateTime Columns called CheckIn and CheckOut and I need to filter it by checking if the CheckIn and CheckOut columns falls in the given 'DateRange' Collection. 

Could anyone please suggest me how to filter my SharePoint list by checking if the CheckIn and CheckOut date Time value falls withing the collection of the DateRange?

Your help is greatly appreciated.

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
WarrenBelz
Super User
Super User

Hi @Anonymous ,

Firstly this is a logic more than a coding exercise and from the information you provided, I am unsure exactly where the dates have to fit in. As an example, if you wanted the whole period within the dates, it would be this

Filter(
   DateRange,
   CheckIn >= StartDate.SelectedDate && 
   CheckOut >= Start.SelectedDate &&
   CheckIn <= EndDate.SelectedDate && 
   CheckIn <= End.SelectedDate
)

There are a lot of other variations using both && (and) and || (or) depending on what you are testing. Can you please specify exactly how the periods are to be compared.

 

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.

View solution in original post

8 REPLIES 8
WarrenBelz
Super User
Super User

Hi @Anonymous ,

Firstly this is a logic more than a coding exercise and from the information you provided, I am unsure exactly where the dates have to fit in. As an example, if you wanted the whole period within the dates, it would be this

Filter(
   DateRange,
   CheckIn >= StartDate.SelectedDate && 
   CheckOut >= Start.SelectedDate &&
   CheckIn <= EndDate.SelectedDate && 
   CheckIn <= End.SelectedDate
)

There are a lot of other variations using both && (and) and || (or) depending on what you are testing. Can you please specify exactly how the periods are to be compared.

 

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.

Anonymous
Not applicable

Hi @WarrenBelz ,

Thank you for replying. My requirement is to filter my sharepoint List called 'Reservations' which has CheckIn and CheckOut columns against a collection which contains all the dates selected between a start and end dates. I want to check if the CheckIn and CheckOut columns falls into any of the dates within the Collection of dates. 

I have created a collection which holds all the date inside the date range along with time using the formula below,

ClearCollect(
DateRange,
AddColumns(
FirstN(
[0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29],
DateDiff(startTime, endTime, Days) + 1),
"Day",
"Day " & (Value + 1),
"CheckRangeFrom",
DateAdd(startTime, Value, Days),
"CheckRangeTo",
DateAdd(endDayTime, Value, Days)
)
);

I need to check if my SharePoint List 'Reservations' columns 'CheckIn' and 'CheckOut' falls between "CheckRangeFrom" and "CheckRangeTo" of my collection "DateRange".

Could you please help me on this?

Thanks for your help!

Hi @Anonymous ,

Have you tried adding the logic I posted to your code? Your code (which I must admit I am a little confused with) is a bit beyond the broad question you posted (which I answered with the logic required). 

Anonymous
Not applicable

@WarrenBelz ,

Could you please let me know what are, StartDate.SelectedDate, Start.SelectedDate, EndDate.SelectedDate and End.SelectedDate in your formula?

Thank you for your help!

Hi @Anonymous ,

I assumed these were data pickers, but they are obviously not. So are you trying to loop through a collection and test each item?

Anonymous
Not applicable

Hi @WarrenBelz , Yes I am trying to loop through the collection which has dateRange. Could you let me know how can I achieve this?

Thank you!

@Anonymous ,

What are the rules you are trying to apply (the full range within, part of the range within or none of the range within)?

WarrenBelz
Super User
Super User

Hi @Anonymous ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

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.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (2,905)