cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MUser
Helper IV
Helper IV

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 III
Super User III

Hi @MUser ,

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 III
Super User III

Hi @MUser ,

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

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 @MUser ,

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). 

@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 @MUser ,

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

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!

@MUser ,

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 III
Super User III

Hi @MUser ,

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
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,337)