cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tommy250
Advocate I
Advocate I

Filter a gallery across 2 dates starting and finishing at 9am

Hi 

 

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.

 

Thanks

5 REPLIES 5
v-bofeng-msft
Community Support
Community Support

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:

 

Filter('List B',Date>=DateAdd(DatePicker1.SelectedDate,9,Hours),Date<=DateAdd(DateAdd(DatePicker1.SelectedDate,9,Hours),1,Days))

 

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

Best Regards,

Bof

Hi @v-bofeng-msft 

 

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.

Filter('List B',Date>=DateAdd(DatePicker1.SelectedDate,-9,Hours),Date<=DateAdd(DateAdd(DatePicker1.SelectedDate,9,Hours),1,Days))

 

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.

 

Cheers

 

Tommy 

Hi @tommy250 :

Do you want to get the time from 9am to 9am the next day? I think this code I gave is enough.

 

Filter('List B',Date>=DateAdd(DatePicker1.SelectedDate,9,Hours),Date<=DateAdd(DateAdd(DatePicker1.SelectedDate,9,Hours),1,Days))

 

In addition,If the result is incorrect, I suggest you check the time zone issue.

  • SP save time as UTC time by default.
  • PowerApps load time as local time by default.

 

Filter('List B',DateAdd(Date,TimeZoneOffset(),Minutes)>=DateAdd(DatePicker1.SelectedDate,9,Hours),DateAdd(Date,TimeZoneOffset(),Minutes)<=DateAdd(DateAdd(DatePicker1.SelectedDate,9,Hours),1,Days))

 

I think this link will help you a lot:

https://powerusers.microsoft.com/t5/Building-Power-Apps/Date-Picker-Sends-Wrong-Date/td-p/488289

Best Regards,

Bof

Hi @v-bofeng-msft 

 

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.

 

Cheers

 

Tommy 

Hi @v-bofeng-msft 

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

 

Thanks

 

Tommy 

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.

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 (2,503)