I'm trying to filter a galley across 2 dates with each day starting and finishing at 9am.
For example I want to select Monday/Tuesday (as a block date with the rest of the week the same i.e. Tuesday/Wednesday, Wednesday/Thursday and so on) and only show items in the gallery from Monday 9am to Tuesday 9am.
I'm currently using the following to select my date but this only shows 1 day at a time and the day starts at 12 midnight.
I do get a Delegation warning stating the filter part of the formula may not work correctly on large data sets.
//Filter gallery using the date picker If( Value(DatePicker.SelectedDate) = 0, 'SP List', Filter( 'SP List', DateDiff( Date, DatePicker.SelectedDate, Days ) = 0 ) )
Any help on this would be much appreciated.
Hi @tommy250 :
Do you want to filter the records between 9:00am on the day selected by datepicker control and 9:00am on the next day?
My method is to add 9 hours to the selected date of the date picker control.
Please try this code:
In addition,If your datasource is SharePoint then I'm afraid there is no way to get around delegation warning (and incomplete results) for large lists when filtering with dates. Similar problems
Cheers for the code, not quite there yet but close. I have changed the first selected date to -9 hours (see below) and this gets me the records on the day I want them to show but also on the next day. I have tried playing around with both Hours in the formula but not having any luck.
With the current code (above), if I add a new record today after 9am it shows up on Today's date (which is what I want) and Yesterday's date (not what I want). I only want to show records added today after 9am to show on today's date and before 9am today to show on Yesterdays date.
Hope that makes sense.
Hi @tommy250 :
Do you want to get the time from 9am to 9am the next day? I think this code I gave is enough.
In addition,If the result is incorrect, I suggest you check the time zone issue.
I think this link will help you a lot:
Thanks for your help on this, still a bit of testing to do by adding a few records after midnight, fingers crossed they appear where they are mean't to be. I have set the SharePoint list to my local time and used your original code but have set it to 8 hours as I'm -60 minutes from UTC. I followed @Shanescows on time and date to figure out how far I'm off UTC.
I also realised that I did not have my date column in SharePoint set to include Time, which didn't help.
I'll update once I get some testing completed.
I'm still not getting the filtered results back that I want.
Using your formula I have modified it (see below) and added 33 hours to last the part of the formula, this gets me part of my desired results with items that that have been added before 9am only showing on the previous day (which is what I want) but items added after 9am show on the correct day but also show on the previous day (not what I want).
I have broken the formula down that I'm using to show my reason for adding the 33 hours. When I highlight any of the DateAdd in the formula bar I get what it equals, see below.
Filter('Medical Take Sheet V2',Date>=DateAdd(PatientOverviewDatePicker.SelectedDate,9,Hours),Date<=DateAdd(DateAdd(PatientOverviewDatePicker.SelectedDate,33,Hours),1,Days))
Date>=DateAdd(PatientOverviewDatePicker.SelectedDate,9,Hours) = 6/8/2020, 9:00:00AM
Date<=DateAdd = 6/8/2020, 12:00:00AM
DateAdd(PatientOverviewDatePicker.SelectedDate,33,Hours),1,Days = 6/8/2020, 6/9/2020, 9:00:00AM
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.
Join us for the next call on June 15, 2022 at 8am PDT.
This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.
Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.