Filter SharePoint List Galery by date

[Anonymous]

Hi All,

I'm creating an app with a galery that's connected to a SharePoint list.

One of the parts of the SharePoint list is a date and time picker.

I've two issues:

1) Filtering and sorting the list

2) Time format is not correct displayed

I want to filter and sort this list with the following settings:

1)  Filter only items from where the date and time isn't passed, to do this, I've used the following command and that's work fine:

Filter('X-Team activiteiten';'Datum activiteit' >= Today())

But I want also to sort on the date field: So the first comming dates are at the top of the galery, to achieve this I Try the follow command, but that's not working:

Filter('X-Team activiteiten';'Datum activiteit' >= Today()) And Sort('X-Team activiteiten';'Datum activiteit';Ascending)

Does anyone how this works?

The second issue is that the times doe not match with the items in the SharePoint list:

Thanks in advance.

[RandyHayes]

@Anonymous 

Try changing the Items property of your gallery to the following:

SortByColumns(
    Filter('X-Team activiteiten';'Datum activiteit' >= Today()); 
    'X-Team activiteiten';Ascending;
    'Datum activiteit';Ascending
    )

That should give you a filtered list of items with dates greater than or equal to today's date (at 12:00 am) and then sorted by the two columns you want.

For your time issue, you need to utilize the TimeZoneOffset function to adjust to the correct time zone.

Change the Text property of those Labels (or whatever control you have for the date in your picture) to something like this:

DateAdd(ThisItem.datefield, −TimeZoneOffset(ThisItem.dateField), Minutes)

(Where datefield is the name of the datefield you are displaying)

I hope this is helpful for you.

[v-xida-msft · Accepted solution]

Hi @Anonymous ,

Could you please share a bit more about your scenario?

Do you want to sort the filtered records based on the 'Datum activiteit' field?

For your first question, I think there is something wrong with the formula you provided. Please consider modify your formula as below:

Sort(
     Filter(
              'X-Team activiteiten';
              'Datum activiteit' >= Today()
     );
     'Datum activiteit';
      Ascending
)

For your second question, based on the issue that you mentioned, I think this issue is related to Time Zone. Please make sure the time zone setting and locale setting of your SP Site are set to same value as your local PC.

On your side, please go to your SP Site Settings, then click "Regional settings", then configure the Time zone setting and Locale setting as below:

Please take a try with above solution, check if the issue is solved.

Best regards,